1# -*- coding: utf-8 -*-
2#
3# Copyright (C) 2004-2021 Edgewall Software
4# All rights reserved.
5#
6# This software is licensed as described in the file COPYING, which
7# you should have received as part of this distribution. The terms
8# are also available at https://trac.edgewall.org/wiki/TracLicense.
9#
10# This software consists of voluntary contributions made by many
11# individuals. For the exact contribution history, see the revision
12# history and logs, available at https://trac.edgewall.org/log/.
13
14from datetime import datetime, timedelta
15import difflib
16import re
17import unittest
18
19from trac.mimeview.api import Mimeview
20from trac.test import Mock, EnvironmentStub, MockPerm, MockRequest
21from trac.ticket.api import TicketSystem
22from trac.ticket.model import Milestone, Severity, Ticket, Version
23from trac.ticket.query import Query, QueryModule, TicketQueryMacro
24from trac.ticket.test import insert_ticket
25from trac.util.datefmt import utc
26from trac.web.api import arg_list_to_args, parse_arg_list
27from trac.web.chrome import Chrome, web_context
28from trac.wiki.formatter import LinkFormatter
29from trac.wiki.tests import formatter
30
31# Note: we don't want to replicate 1:1 all the SQL dialect abstraction
32#       methods from the trac.db layer here.
33
34class QueryTestCase(unittest.TestCase):
35
36    n_tickets = 10
37
38    def prettifySQL(self, sql):
39        """Returns a prettified version of the SQL as a list of lines to help
40        in creating a useful diff between two SQL statements."""
41        pretty = []
42        for line in sql.split('\n'):
43            if line.startswith('SELECT '):
44                pretty.extend(x + '\n'
45                              for x in ',\n'.join(line.split(',')).split('\n'))
46            else:
47                pretty.append(line + '\n')
48        return pretty
49
50    def assertEqualSQL(self, sql, correct_sql):
51        sql_split = self.prettifySQL(sql)
52        correct_sql_split = self.prettifySQL(correct_sql)
53        sql_diff = ''.join(list(
54            difflib.unified_diff(correct_sql_split, sql_split)
55        ))
56        failure_message = "%r != %r\n" % (sql, correct_sql) + sql_diff
57        self.assertEqual(sql, correct_sql, failure_message)
58
59    def setUp(self):
60        self.env = EnvironmentStub(default_data=True)
61        self.req = MockRequest(self.env)
62        self.query_module = QueryModule(self.env)
63        self.tktids = self._insert_tickets(
64            owner=[None, '', 'someone', 'someone_else', 'none'],
65            type=[None, '', 'enhancement', 'defect', 'task'],
66            status=[None, '', 'new', 'assigned', 'reopened', 'closed'],
67            priority=[None, '', 'blocker', 'critical', 'major', 'minor',
68                      'trivial'],
69            milestone=[None, '', 'milestone1', 'milestone2'],
70            version=[None, '', '0.0', 'version1', '1.0', '2.0'],
71            keywords=[None, '', 'foo', 'bar', 'baz', 'foo bar', 'bar baz',
72                      'foo baz', 'foo bar baz'])
73        dt = datetime(2008, 7, 1, 12, tzinfo=utc)
74        with self.env.db_transaction:
75            for name in ('milestone1', 'milestone2'):
76                milestone = Milestone(self.env, name)
77                milestone.due = dt
78                milestone.update()
79            for name in ('1.0', '2.0'):
80                version = Version(self.env, name)
81                version.time = dt
82                version.update()
83            for name in ('urgent', 'high', 'medium'):
84                severity = Severity(self.env)
85                severity.name = name
86                severity.insert()
87        tktsys = TicketSystem(self.env)
88        tktsys.reset_ticket_fields()
89        del tktsys.custom_fields
90
91    def tearDown(self):
92        self.env.reset_db()
93
94    def _insert_tickets(self, owner, type, status, priority, milestone,
95                        version, keywords):
96        when = datetime(2008, 7, 1, 12, 34, 56, 987654, utc)
97        with self.env.db_transaction:
98            ids = []
99            for idx in range(self.n_tickets):
100                t = insert_ticket(self.env, summary='Summary %d' % idx,
101                                  owner=owner[idx % len(owner)],
102                                  type=type[idx % len(type)],
103                                  status=status[idx % len(status)],
104                                  priority=priority[idx % len(priority)],
105                                  milestone=milestone[idx % len(milestone)],
106                                  version=version[idx % len(version)],
107                                  keywords=keywords[idx % len(keywords)],
108                                  when=when + timedelta(days=idx * 10))
109                ids.append(t.id)
110                t.save_changes(comment='...',
111                               when=when + timedelta(days=idx * 10 + 1))
112        return ids
113
114    def _update_tickets(self, name, values):
115        with self.env.db_transaction:
116            for idx, tktid in enumerate(self.tktids):
117                t = Ticket(self.env, tktid)
118                t[name] = values[idx % len(values)]
119                t.save_changes()
120
121    def _execute_query(self, query):
122        tickets = query.execute(self.req)
123        self.assertEqual(tickets, query.execute(self.req, cached_ids=[0]))
124        return tickets
125
126    def _process_request(self, req):
127        self.assertTrue(self.query_module.match_request(req))
128        return self.query_module.process_request(req)
129
130    def test_all_ordered_by_id(self):
131        query = Query(self.env, order='id')
132        sql, args = query.get_sql()
133        self.assertEqualSQL(sql,
134"""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
135FROM ticket AS t
136  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
137ORDER BY COALESCE(t.id,0)=0,t.id""")
138        self.assertEqual([], args)
139        tickets = self._execute_query(query)
140        self.assertEqual(self.n_tickets, len(tickets))
141        self.assertTrue(tickets[0]['id'] < tickets[-1]['id'])
142
143    def test_all_ordered_by_id_desc(self):
144        query = Query(self.env, order='id', desc=1)
145        sql, args = query.get_sql()
146        self.assertEqualSQL(sql,
147"""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
148FROM ticket AS t
149  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
150ORDER BY COALESCE(t.id,0)=0 DESC,t.id DESC""")
151        self.assertEqual([], args)
152        tickets = self._execute_query(query)
153        self.assertEqual(self.n_tickets, len(tickets))
154        self.assertTrue(tickets[0]['id'] > tickets[-1]['id'])
155
156    def test_all_ordered_by_id_verbose(self):
157        query = Query(self.env, order='id', verbose=1)
158        sql, args = query.get_sql()
159        self.assertEqualSQL(sql,
160"""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
161FROM ticket AS t
162  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
163ORDER BY COALESCE(t.id,0)=0,t.id""")
164        self.assertEqual([], args)
165        tickets = self._execute_query(query)
166        self.assertEqual(self.n_tickets, len(tickets))
167
168    def test_all_ordered_by_id_from_unicode(self):
169        query = Query.from_string(self.env, 'order=id')
170        sql, args = query.get_sql()
171        self.assertEqualSQL(sql,
172"""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
173FROM ticket AS t
174  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
175ORDER BY COALESCE(t.id,0)=0,t.id""")
176        self.assertEqual([], args)
177        tickets = self._execute_query(query)
178        self.assertEqual(self.n_tickets, len(tickets))
179
180    def test_all_ordered_by_priority(self):
181        query = Query(self.env)  # priority is default order
182        sql, args = query.get_sql()
183        with self.env.db_query as db:
184            cast_priority = db.cast('priority.value', 'int')
185        self.assertEqualSQL(sql,
186"""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
187FROM ticket AS t
188  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
189ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % {
190          'cast_priority': cast_priority})
191        self.assertEqual([], args)
192        tickets = self._execute_query(query)
193        self.assertEqual(['blocker', 'blocker', 'critical', 'major', 'minor',
194                          'trivial', '', '', '', ''],
195                         [t['priority'] for t in tickets])
196
197    def test_all_ordered_by_priority_desc(self):
198        query = Query(self.env, desc=1) # priority is default order
199        sql, args = query.get_sql()
200        with self.env.db_query as db:
201            cast_priority = db.cast('priority.value', 'int')
202        self.assertEqualSQL(sql,
203"""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
204FROM ticket AS t
205  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
206ORDER BY COALESCE(priority.value,'')='' DESC,%(cast_priority)s DESC,t.id""" % {
207          'cast_priority': cast_priority})
208        self.assertEqual([], args)
209        tickets = self._execute_query(query)
210        self.assertEqual(['', '', '', '', 'trivial', 'minor', 'major',
211                          'critical', 'blocker', 'blocker'],
212                         [t['priority'] for t in tickets])
213
214    def test_all_ordered_by_version(self):
215        query = Query(self.env, order='version')
216        sql, args = query.get_sql()
217        self.assertEqualSQL(sql,
218"""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
219FROM ticket AS t
220  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
221  LEFT OUTER JOIN version ON (version.name=version)
222ORDER BY COALESCE(t.version,'')='',COALESCE(version.time,0)=0,version.time,t.version,t.id""")
223        self.assertEqual([], args)
224        tickets = self._execute_query(query)
225        self.assertEqual(['1.0', '2.0', '0.0', '0.0', 'version1', 'version1',
226                          '', '', '', ''],
227                         [t['version'] for t in tickets])
228
229    def test_all_ordered_by_version_desc(self):
230        query = Query(self.env, order='version', desc=1)
231        sql, args = query.get_sql()
232        self.assertEqualSQL(sql,
233"""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
234FROM ticket AS t
235  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
236  LEFT OUTER JOIN version ON (version.name=version)
237ORDER BY COALESCE(t.version,'')='' DESC,COALESCE(version.time,0)=0 DESC,version.time DESC,t.version DESC,t.id""")
238        self.assertEqual([], args)
239        tickets = self._execute_query(query)
240        self.assertEqual(['', '', '', '', 'version1', 'version1', '0.0', '0.0',
241                          '2.0', '1.0'],
242                         [t['version'] for t in tickets])
243
244    def test_ordered_by_type(self):
245        casts = {}
246        with self.env.db_transaction as db:
247            db("UPDATE enum SET value=2 "
248               "WHERE type='ticket_type' AND name='defect'")
249            db("UPDATE enum SET value=3 "
250               "WHERE type='ticket_type' AND name='enhancement'")
251            db("UPDATE enum SET value=1 "
252               "WHERE type='ticket_type' AND name='task'")
253            casts['cast_type_value'] = db.cast('type.value', 'int')
254
255        query = Query(self.env, order='type')
256        sql, args = query.get_sql()
257        self.assertEqualSQL(sql, """\
258SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,\
259t.status AS status,t.priority AS priority,t.milestone AS milestone,\
260t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
261FROM ticket AS t
262  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
263  LEFT OUTER JOIN enum AS type ON (type.type='ticket_type' AND type.name=t.type)
264ORDER BY COALESCE(type.value,'')='',%(cast_type_value)s,t.id""" % casts)
265        self.assertEqual([], args)
266        tickets = self._execute_query(query)
267        self.assertEqual(['task', 'task', 'defect', 'defect', 'enhancement',
268                          'enhancement', '', '', '', ''],
269                         [t['type'] for t in tickets])
270
271    def test_constrained_by_milestone(self):
272        query = Query.from_string(self.env, 'milestone=milestone1', order='id')
273        sql, args = query.get_sql()
274        self.assertEqualSQL(sql,
275"""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.component AS component,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,priority.value AS _priority_value
276FROM ticket AS t
277  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
278WHERE ((COALESCE(t.milestone,'')=%s))
279ORDER BY COALESCE(t.id,0)=0,t.id""")
280        self.assertEqual(['milestone1'], args)
281        tickets = self._execute_query(query)
282        self.assertEqual(['milestone1', 'milestone1'],
283                         [t['milestone'] for t in tickets])
284
285    def test_all_grouped_by_milestone(self):
286        query = Query(self.env, order='id', group='milestone')
287        sql, args = query.get_sql()
288        self.assertEqualSQL(sql,
289"""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.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
290FROM ticket AS t
291  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
292  LEFT OUTER JOIN milestone ON (milestone.name=milestone)
293ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,milestone.completed,COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(t.id,0)=0,t.id""")
294        self.assertEqual([], args)
295        tickets = self._execute_query(query)
296        self.assertEqual(['milestone1', 'milestone1', 'milestone2',
297                          'milestone2', '', '', '', '', '', ''],
298                         [t['milestone'] for t in tickets])
299
300    def test_all_grouped_by_milestone_desc(self):
301        query = Query(self.env, order='id', group='milestone', groupdesc=1)
302        sql, args = query.get_sql()
303        self.assertEqualSQL(sql,
304"""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.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
305FROM ticket AS t
306  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
307  LEFT OUTER JOIN milestone ON (milestone.name=milestone)
308ORDER 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""")
309        self.assertEqual([], args)
310        tickets = self._execute_query(query)
311        self.assertEqual(['', '', '', '', '', '', 'milestone2', 'milestone2',
312                          'milestone1', 'milestone1'],
313                         [t['milestone'] for t in tickets])
314
315    def test_grouped_by_priority(self):
316        query = Query(self.env, group='priority')
317        sql, args = query.get_sql()
318        with self.env.db_query as db:
319            cast_priority = db.cast('priority.value', 'int')
320        self.assertEqualSQL(sql,
321"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.milestone AS milestone,t.component AS component,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
322FROM ticket AS t
323  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
324ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % {
325          'cast_priority': cast_priority})
326        self.assertEqual([], args)
327        tickets = self._execute_query(query)
328        self.assertEqual(['blocker', 'blocker', 'critical', 'major', 'minor',
329                          'trivial', '', '', '', ''],
330                         [t['priority'] for t in tickets])
331
332    def test_constrained_by_milestone_not(self):
333        query = Query.from_string(self.env, 'milestone!=milestone1', order='id')
334        sql, args = query.get_sql()
335        self.assertEqualSQL(sql,
336"""SELECT t.id AS id,t.summary AS summary,t.milestone AS milestone,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
337FROM ticket AS t
338  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
339WHERE ((COALESCE(t.milestone,'')!=%s))
340ORDER BY COALESCE(t.id,0)=0,t.id""")
341        self.assertEqual(['milestone1'], args)
342        tickets = self._execute_query(query)
343        self.assertEqual(['', '', 'milestone2', '', '', 'milestone2', '', ''],
344                         [t['milestone'] for t in tickets])
345
346    def test_constrained_by_status(self):
347        query = Query.from_string(self.env, 'status=new|assigned|reopened',
348                                  order='id')
349        sql, args = query.get_sql()
350        self.assertEqualSQL(sql,
351"""SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
352FROM ticket AS t
353  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
354WHERE (COALESCE(t.status,'') IN (%s,%s,%s))
355ORDER BY COALESCE(t.id,0)=0,t.id""")
356        self.assertEqual(['new', 'assigned', 'reopened'], args)
357        tickets = self._execute_query(query)
358        self.assertEqual(['new', 'assigned', 'reopened', 'new', 'assigned'],
359                         [t['status'] for t in tickets])
360
361    def test_constrained_by_owner_containing(self):
362        query = Query.from_string(self.env, 'owner~=someone', order='id')
363        sql, args = query.get_sql()
364        with self.env.db_query as db:
365            like = db.like()
366        self.assertEqualSQL(sql,
367"""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
368FROM ticket AS t
369  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
370WHERE ((COALESCE(t.owner,'') %(like)s))
371ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': like})
372        self.assertEqual(['%someone%'], args)
373        tickets = self._execute_query(query)
374        self.assertEqual(['someone', 'someone_else', 'someone',
375                          'someone_else'],
376                         [t['owner'] for t in tickets])
377
378    def test_constrained_by_owner_not_containing(self):
379        query = Query.from_string(self.env, 'owner!~=someone', order='id')
380        sql, args = query.get_sql()
381        with self.env.db_query as db:
382            like = db.like()
383        self.assertEqualSQL(sql,
384"""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
385FROM ticket AS t
386  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
387WHERE ((COALESCE(t.owner,'') NOT %(like)s))
388ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': like})
389        self.assertEqual(['%someone%'], args)
390        tickets = self._execute_query(query)
391        self.assertEqual(['', '', 'none', '', '', 'none'],
392                         [t['owner'] for t in tickets])
393
394    def test_constrained_by_owner_beginswith(self):
395        query = Query.from_string(self.env, 'owner^=someone', order='id')
396        sql, args = query.get_sql()
397        with self.env.db_query as db:
398            like = db.like()
399        self.assertEqualSQL(sql,
400"""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
401FROM ticket AS t
402  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
403WHERE ((COALESCE(t.owner,'') %(like)s))
404ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': like})
405        self.assertEqual(['someone%'], args)
406        tickets = self._execute_query(query)
407        self.assertEqual(['someone', 'someone_else', 'someone',
408                          'someone_else'],
409                         [t['owner'] for t in tickets])
410
411    def test_constrained_by_owner_endswith(self):
412        query = Query.from_string(self.env, 'owner$=someone', order='id')
413        sql, args = query.get_sql()
414        with self.env.db_query as db:
415            like = db.like()
416        self.assertEqualSQL(sql,
417"""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
418FROM ticket AS t
419  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
420WHERE ((COALESCE(t.owner,'') %(like)s))
421ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': like})
422        self.assertEqual(['%someone'], args)
423        tickets = self._execute_query(query)
424        self.assertEqual(['someone', 'someone'], [t['owner'] for t in tickets])
425
426    def test_constrained_by_custom_field(self):
427        self.env.config.set('ticket-custom', 'foo', 'text')
428        self._update_tickets('foo', [None, '', 'something'])
429        query = Query.from_string(self.env, 'foo=something', order='id')
430        sql, args = query.get_sql()
431        with self.env.db_query as db:
432            foo = db.quote('foo')
433        self.assertEqualSQL(sql,
434"""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)s.value AS %(foo)s
435FROM ticket AS t
436  LEFT OUTER JOIN ticket_custom AS %(foo)s ON (%(foo)s.ticket=t.id AND %(foo)s.name='foo')
437  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
438WHERE ((COALESCE(%(foo)s.value,'')=%%s))
439ORDER BY COALESCE(t.id,0)=0,t.id""" % {'foo': foo})
440        self.assertEqual(['something'], args)
441        tickets = self._execute_query(query)
442        self.assertEqual(['something'] * 3, [t['foo'] for t in tickets])
443
444    def test_grouped_by_custom_field(self):
445        self.env.config.set('ticket-custom', 'foo', 'text')
446        self._update_tickets('foo', [None, '', 'something'])
447        query = Query(self.env, group='foo', order='id')
448        sql, args = query.get_sql()
449        with self.env.db_query as db:
450            foo = db.quote('foo')
451        self.assertEqualSQL(sql,
452"""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)s.value AS %(foo)s
453FROM ticket AS t
454  LEFT OUTER JOIN ticket_custom AS %(foo)s ON (%(foo)s.ticket=t.id AND %(foo)s.name='foo')
455  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
456ORDER BY COALESCE(%(foo)s.value,'')='',%(foo)s.value,COALESCE(t.id,0)=0,t.id"""
457        % {'foo': foo})
458        self.assertEqual([], args)
459        tickets = self._execute_query(query)
460        self.assertEqual(['something'] * 3 + [''] * 7,
461                         [t['foo'] for t in tickets])
462
463    def test_constrained_by_id_ranges(self):
464        query = Query.from_string(self.env, 'id=42,44,51-55&order=id')
465        sql, args = query.get_sql()
466        self.assertEqualSQL(sql,
467"""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
468FROM ticket AS t
469  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
470WHERE ((t.id BETWEEN %s AND %s OR t.id IN (42,44)))
471ORDER BY COALESCE(t.id,0)=0,t.id""")
472        self.assertEqual([51, 55], args)
473
474    def test_constrained_by_id_and_custom_field(self):
475        self.env.config.set('ticket-custom', 'foo', 'text')
476        ticket = insert_ticket(self.env, reporter='joe', summary='Foo',
477                               foo='blah')
478
479        query = Query.from_string(self.env, 'id=%d-42&foo=blah' % ticket.id)
480        tickets = self._execute_query(query)
481        self.assertEqual(1, len(tickets))
482        self.assertEqual(ticket.id, tickets[0]['id'])
483
484        query = Query.from_string(self.env, 'id=%d,42&foo=blah' % ticket.id)
485        tickets = self._execute_query(query)
486        self.assertEqual(1, len(tickets))
487        self.assertEqual(ticket.id, tickets[0]['id'])
488
489        query = Query.from_string(self.env, 'id=%d,42,43-84&foo=blah' %
490                                            ticket.id)
491        tickets = self._execute_query(query)
492        self.assertEqual(1, len(tickets))
493        self.assertEqual(ticket.id, tickets[0]['id'])
494
495    def test_too_many_custom_fields(self):
496        fields = ['col_%02d' % i for i in range(100)]
497        for f in fields:
498            self.env.config.set('ticket-custom', f, 'text')
499
500        ticket = insert_ticket(self.env, reporter='joe', summary='Foo',
501                               **{f: '%d.%s' % (idx, f)
502                                  for idx, f in enumerate(fields)})
503
504        string = 'col_00=0.col_00&order=id&col=id&col=reporter&col=summary' + \
505                 ''.join('&col=' + f for f in fields)
506        query = Query.from_string(self.env, string)
507        tickets = self._execute_query(query)
508        self.assertEqual(ticket.id, tickets[0]['id'])
509        self.assertEqual('joe', tickets[0]['reporter'])
510        self.assertEqual('Foo', tickets[0]['summary'])
511        self.assertEqual('0.col_00', tickets[0]['col_00'])
512        self.assertEqual('99.col_99', tickets[0]['col_99'])
513
514        query = Query.from_string(self.env, 'col_00=notfound')
515        self.assertEqual([], query.execute(self.req))
516
517    def test_constrained_by_multiple_owners(self):
518        query = Query.from_string(self.env, 'owner=someone|someone_else',
519                                  order='id')
520        sql, args = query.get_sql()
521        self.assertEqualSQL(sql,
522"""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
523FROM ticket AS t
524  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
525WHERE (COALESCE(t.owner,'') IN (%s,%s))
526ORDER BY COALESCE(t.id,0)=0,t.id""")
527        self.assertEqual(['someone', 'someone_else'], args)
528        tickets = self._execute_query(query)
529        self.assertEqual(['someone', 'someone_else', 'someone',
530                          'someone_else'],
531                         [t['owner'] for t in tickets])
532
533    def test_constrained_by_multiple_owners_not(self):
534        query = Query.from_string(self.env, 'owner!=someone|someone_else',
535                                  order='id')
536        sql, args = query.get_sql()
537        self.assertEqualSQL(sql,
538"""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
539FROM ticket AS t
540  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
541WHERE (COALESCE(t.owner,'') NOT IN (%s,%s))
542ORDER BY COALESCE(t.id,0)=0,t.id""")
543        self.assertEqual(['someone', 'someone_else'], args)
544        tickets = self._execute_query(query)
545        self.assertEqual(['', '', 'none', '', '', 'none'],
546                         [t['owner'] for t in tickets])
547
548    def test_constrained_by_multiple_owners_contain(self):
549        query = Query.from_string(self.env, 'owner~=someone|someone_else',
550                                  order='id')
551        sql, args = query.get_sql()
552        with self.env.db_query as db:
553            like = db.like()
554        self.assertEqual(['%someone%', '%someone/_else%'], args)
555        self.assertEqualSQL(sql,
556"""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
557FROM ticket AS t
558  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
559WHERE ((COALESCE(t.owner,'') %(like)s OR COALESCE(t.owner,'') %(like)s))
560ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': like})
561        tickets = self._execute_query(query)
562        self.assertEqual(['someone', 'someone_else', 'someone',
563                          'someone_else'],
564                         [t['owner'] for t in tickets])
565
566    def test_constrained_by_an_empty_value(self):
567        query = Query.from_string(self.env, 'owner=', order='id')
568        sql, args = query.get_sql()
569        self.assertEqualSQL(sql,
570"""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
571FROM ticket AS t
572  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
573WHERE ((COALESCE(t.owner,'')=%s))
574ORDER BY COALESCE(t.id,0)=0,t.id""")
575        self.assertEqual([''], args)
576        tickets = self._execute_query(query)
577        self.assertEqual(['', '', '', ''], [t['owner'] for t in tickets])
578
579    def test_constrained_by_an_empty_value_not(self):
580        query = Query.from_string(self.env, 'owner!=', order='id')
581        sql, args = query.get_sql()
582        self.assertEqualSQL(sql,
583"""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
584FROM ticket AS t
585  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
586WHERE ((COALESCE(t.owner,'')!=%s))
587ORDER BY COALESCE(t.id,0)=0,t.id""")
588        self.assertEqual([''], args)
589        tickets = self._execute_query(query)
590        self.assertEqual(['someone', 'someone_else', 'none', 'someone',
591                          'someone_else', 'none'],
592                         [t['owner'] for t in tickets])
593
594    def test_constrained_by_empty_values(self):
595        query = Query.from_string(self.env, 'owner=|', order='id')
596        sql, args = query.get_sql()
597        self.assertEqualSQL(sql,
598"""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
599FROM ticket AS t
600  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
601WHERE (COALESCE(t.owner,'') IN (%s,%s))
602ORDER BY COALESCE(t.id,0)=0,t.id""")
603        self.assertEqual(['', ''], args)
604        tickets = self._execute_query(query)
605        self.assertEqual(['', '', '', ''], [t['owner'] for t in tickets])
606
607    def test_constrained_by_empty_values_not(self):
608        query = Query.from_string(self.env, 'owner!=|', order='id')
609        sql, args = query.get_sql()
610        self.assertEqualSQL(sql,
611"""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
612FROM ticket AS t
613  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
614WHERE (COALESCE(t.owner,'') NOT IN (%s,%s))
615ORDER BY COALESCE(t.id,0)=0,t.id""")
616        self.assertEqual(['', ''], args)
617        tickets = self._execute_query(query)
618        self.assertEqual(['someone', 'someone_else', 'none', 'someone',
619                          'someone_else', 'none'],
620                         [t['owner'] for t in tickets])
621
622    def test_constrained_by_empty_value_contains(self):
623        query = Query.from_string(self.env, 'owner~=|', order='id')
624        sql, args = query.get_sql()
625        self.assertEqualSQL(sql,
626"""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
627FROM ticket AS t
628  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
629ORDER BY COALESCE(t.id,0)=0,t.id""")
630        self.assertEqual([], args)
631        tickets = self._execute_query(query)
632        self.assertEqual(['', '', 'someone', 'someone_else', 'none', '', '',
633                          'someone', 'someone_else', 'none'],
634                         [t['owner'] for t in tickets])
635
636    def test_constrained_by_empty_value_startswith(self):
637        query = Query.from_string(self.env, 'owner^=|', order='id')
638        sql, args = query.get_sql()
639        self.assertEqualSQL(sql,
640"""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
641FROM ticket AS t
642  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
643ORDER BY COALESCE(t.id,0)=0,t.id""")
644        self.assertEqual([], args)
645        tickets = self._execute_query(query)
646        self.assertEqual(['', '', 'someone', 'someone_else', 'none', '', '',
647                          'someone', 'someone_else', 'none'],
648                         [t['owner'] for t in tickets])
649
650    def test_constrained_by_empty_value_endswith(self):
651        query = Query.from_string(self.env, 'owner$=|', order='id')
652        sql, args = query.get_sql()
653        self.assertEqualSQL(sql,
654"""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
655FROM ticket AS t
656  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
657ORDER BY COALESCE(t.id,0)=0,t.id""")
658        self.assertEqual([], args)
659        tickets = self._execute_query(query)
660        self.assertEqual(['', '', 'someone', 'someone_else', 'none', '', '',
661                          'someone', 'someone_else', 'none'],
662                         [t['owner'] for t in tickets])
663
664    def test_constrained_by_time_range(self):
665        query = Query.from_string(self.env, 'created=2008-08-01..2008-09-01', order='id')
666        sql, args = query.get_sql(self.req)
667        self.assertEqualSQL(sql,
668"""SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS _priority_value
669FROM ticket AS t
670  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
671WHERE (((t.time>=%s AND t.time<%s)))
672ORDER BY COALESCE(t.id,0)=0,t.id""")
673        self.assertEqual([1217548800000000, 1220227200000000], args)
674        tickets = self._execute_query(query)
675        self.assertEqual(['2008-08-10T12:34:56.987654+00:00',
676                          '2008-08-20T12:34:56.987654+00:00',
677                          '2008-08-30T12:34:56.987654+00:00'],
678                         [t['time'].isoformat() for t in tickets])
679
680    def test_constrained_by_time_range_exclusion(self):
681        query = Query.from_string(self.env, 'created!=2008-08-01..2008-09-01', order='id')
682        sql, args = query.get_sql(self.req)
683        self.assertEqualSQL(sql,
684"""SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS _priority_value
685FROM ticket AS t
686  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
687WHERE ((NOT (t.time>=%s AND t.time<%s)))
688ORDER BY COALESCE(t.id,0)=0,t.id""")
689        self.assertEqual([1217548800000000, 1220227200000000], args)
690        tickets = self._execute_query(query)
691        self.assertEqual(['2008-07-01T12:34:56.987654+00:00',
692                          '2008-07-11T12:34:56.987654+00:00',
693                          '2008-07-21T12:34:56.987654+00:00',
694                          '2008-07-31T12:34:56.987654+00:00',
695                          '2008-09-09T12:34:56.987654+00:00',
696                          '2008-09-19T12:34:56.987654+00:00',
697                          '2008-09-29T12:34:56.987654+00:00'],
698                         [t['time'].isoformat() for t in tickets])
699
700    def test_constrained_by_time_range_open_right(self):
701        query = Query.from_string(self.env, 'created=2008-08-01..', order='id')
702        sql, args = query.get_sql(self.req)
703        self.assertEqualSQL(sql,
704"""SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS _priority_value
705FROM ticket AS t
706  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
707WHERE ((t.time>=%s))
708ORDER BY COALESCE(t.id,0)=0,t.id""")
709        self.assertEqual([1217548800000000], args)
710        tickets = self._execute_query(query)
711        self.assertEqual(['2008-08-10T12:34:56.987654+00:00',
712                          '2008-08-20T12:34:56.987654+00:00',
713                          '2008-08-30T12:34:56.987654+00:00',
714                          '2008-09-09T12:34:56.987654+00:00',
715                          '2008-09-19T12:34:56.987654+00:00',
716                          '2008-09-29T12:34:56.987654+00:00'],
717                         [t['time'].isoformat() for t in tickets])
718
719    def test_constrained_by_time_range_open_left(self):
720        query = Query.from_string(self.env, 'created=..2008-09-01', order='id')
721        sql, args = query.get_sql(self.req)
722        self.assertEqualSQL(sql,
723"""SELECT t.id AS id,t.summary AS summary,t.time AS time,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.changetime AS changetime,priority.value AS _priority_value
724FROM ticket AS t
725  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
726WHERE ((t.time<%s))
727ORDER BY COALESCE(t.id,0)=0,t.id""")
728        self.assertEqual([1220227200000000], args)
729        tickets = self._execute_query(query)
730        self.assertEqual(['2008-07-01T12:34:56.987654+00:00',
731                          '2008-07-11T12:34:56.987654+00:00',
732                          '2008-07-21T12:34:56.987654+00:00',
733                          '2008-07-31T12:34:56.987654+00:00',
734                          '2008-08-10T12:34:56.987654+00:00',
735                          '2008-08-20T12:34:56.987654+00:00',
736                          '2008-08-30T12:34:56.987654+00:00'],
737                         [t['time'].isoformat() for t in tickets])
738
739    def test_constrained_by_time_range_modified(self):
740        query = Query.from_string(self.env, 'modified=2008-08-01..2008-09-01', order='id')
741        sql, args = query.get_sql(self.req)
742        self.assertEqualSQL(sql,
743"""SELECT t.id AS id,t.summary AS summary,t.changetime AS changetime,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,priority.value AS _priority_value
744FROM ticket AS t
745  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
746WHERE (((t.changetime>=%s AND t.changetime<%s)))
747ORDER BY COALESCE(t.id,0)=0,t.id""")
748        self.assertEqual([1217548800000000, 1220227200000000], args)
749        tickets = self._execute_query(query)
750        self.assertEqual(['2008-08-01T12:34:56.987654+00:00',
751                          '2008-08-11T12:34:56.987654+00:00',
752                          '2008-08-21T12:34:56.987654+00:00',
753                          '2008-08-31T12:34:56.987654+00:00'],
754                         [t['changetime'].isoformat() for t in tickets])
755
756    def test_constrained_by_keywords(self):
757        query = Query.from_string(self.env, 'keywords~=foo -bar baz',
758                                  order='id')
759        sql, args = query.get_sql()
760        with self.env.db_query as db:
761            like = db.like()
762        self.assertEqualSQL(sql,
763"""SELECT t.id AS id,t.summary AS summary,t.keywords AS keywords,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
764FROM ticket AS t
765  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
766WHERE (((COALESCE(t.keywords,'') %(like)s AND COALESCE(t.keywords,'') NOT %(like)s AND COALESCE(t.keywords,'') %(like)s)))
767ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': like})
768        self.assertEqual(['%foo%', '%bar%', '%baz%'], args)
769        tickets = self._execute_query(query)
770        self.assertEqual(['foo baz'], [t['keywords'] for t in tickets])
771
772    def test_constrained_by_keywords_not(self):
773        query = Query.from_string(self.env, 'keywords!~=foo -bar baz',
774                                  order='id')
775        sql, args = query.get_sql()
776        with self.env.db_query as db:
777            like = db.like()
778        self.assertEqualSQL(sql,
779"""SELECT t.id AS id,t.summary AS summary,t.keywords AS keywords,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
780FROM ticket AS t
781  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
782WHERE ((NOT (COALESCE(t.keywords,'') %(like)s AND COALESCE(t.keywords,'') NOT %(like)s AND COALESCE(t.keywords,'') %(like)s)))
783ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': like})
784        self.assertEqual(['%foo%', '%bar%', '%baz%'], args)
785        tickets = self._execute_query(query)
786        self.assertEqual(['', '', 'foo', 'bar', 'baz', 'foo bar', 'bar baz',
787                          'foo bar baz', ''],
788                         [t['keywords'] for t in tickets])
789
790    def test_constrained_by_keyword_phrase(self):
791        query = Query.from_string(self.env, 'keywords~="bar baz" -foo',
792                                  order='id')
793        sql, args = query.get_sql()
794        with self.env.db_query as db:
795            like = db.like()
796        self.assertEqualSQL(sql,
797"""SELECT t.id AS id,t.summary AS summary,t.keywords AS keywords,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
798FROM ticket AS t
799  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
800WHERE (((COALESCE(t.keywords,'') %(like)s AND COALESCE(t.keywords,'') NOT %(like)s)))
801ORDER BY COALESCE(t.id,0)=0,t.id""" % {'like': like})
802        self.assertEqual(['%bar baz%', '%foo%'], args)
803        tickets = query.execute(self.req)
804        self.assertEqual(['bar baz'], [t['keywords'] for t in tickets])
805
806    def test_constrained_by_milestone_or_version(self):
807        query = Query.from_string(self.env, 'milestone=milestone1&or&version=version1', order='id')
808        sql, args = query.get_sql()
809        self.assertEqualSQL(sql,
810"""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.component AS component,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,t.version AS version,priority.value AS _priority_value
811FROM ticket AS t
812  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
813WHERE ((COALESCE(t.milestone,'')=%s)) OR ((COALESCE(t.version,'')=%s))
814ORDER BY COALESCE(t.id,0)=0,t.id""")
815        self.assertEqual(['milestone1', 'version1'], args)
816        tickets = self._execute_query(query)
817        self.assertEqual([('milestone1', '0.0'),
818                          ('milestone2', 'version1'),
819                          ('milestone1', ''),
820                          ('',           'version1')],
821                         [(t['milestone'], t['version']) for t in tickets])
822
823    def test_equal_in_value(self):
824        query = Query.from_string(self.env, r'status=this=that&version=version1',
825                                  order='id')
826        sql, args = query.get_sql()
827        self.assertEqualSQL(sql,
828"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.component AS component,t.status AS status,t.time AS time,t.changetime AS changetime,t.version AS version,priority.value AS _priority_value
829FROM ticket AS t
830  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
831WHERE ((COALESCE(t.status,'')=%s) AND (COALESCE(t.version,'')=%s))
832ORDER BY COALESCE(t.id,0)=0,t.id""")
833        self.assertEqual(['this=that', 'version1'], args)
834        tickets = self._execute_query(query)
835
836    def test_special_character_escape(self):
837        query = Query.from_string(self.env, r'status=here\&now|maybe\|later|back\slash',
838                                  order='id')
839        sql, args = query.get_sql()
840        self.assertEqualSQL(sql,
841"""SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS _priority_value
842FROM ticket AS t
843  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
844WHERE (COALESCE(t.status,'') IN (%s,%s,%s))
845ORDER BY COALESCE(t.id,0)=0,t.id""")
846        self.assertEqual(['here&now', 'maybe|later', 'back\\slash'], args)
847        tickets = self._execute_query(query)
848
849    def test_repeated_constraint_field(self):
850        like_query = Query.from_string(self.env, 'owner!=someone|someone_else',
851                                       order='id')
852        query = Query.from_string(self.env, 'owner!=someone&owner!=someone_else',
853                                  order='id')
854        like_sql, like_args = like_query.get_sql()
855        sql, args = query.get_sql()
856        self.assertEqualSQL(sql, like_sql)
857        self.assertEqual(args, like_args)
858        tickets = self._execute_query(query)
859
860    def test_priority_value_in_custom_field(self):
861        self.env.config.set('ticket-custom', 'priority_value', 'text')
862        self._update_tickets('priority_value', [None, 'foo', 'bar', 'baz'])
863        query = Query.from_string(self.env,
864                                  'priority_value=baz&priority_value=foo')
865        tickets = query.execute()
866        self.assertEqual({'foo', 'baz'},
867                         {t['priority_value'] for t in tickets})
868        self.assertIn(tickets[0]['_priority_value'],
869                      (None, '1', '2', '3', '4', '5'))
870
871    def test_user_var(self):
872        query = Query.from_string(self.env, 'owner=$USER&order=id')
873        sql, args = query.get_sql(req=self.req)
874        self.assertEqualSQL(sql,
875"""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
876FROM ticket AS t
877  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
878WHERE ((COALESCE(t.owner,'')=%s))
879ORDER BY COALESCE(t.id,0)=0,t.id""")
880        self.assertEqual(['anonymous'], args)
881        tickets = self._execute_query(query)
882
883    def _setup_no_defined_values_and_custom_field(self, name):
884        quoted = {}
885        self.env.config.set('ticket-custom', name, 'text')
886        with self.env.db_transaction as db:
887            if name in ('milestone', 'version'):
888                db("DELETE FROM %s" % name)
889            else:
890                db("DELETE FROM enum WHERE type=%s",
891                   (name if name != 'type' else 'ticket_type',))
892        tktsys = TicketSystem(self.env)
893        tktsys.reset_ticket_fields()
894        del tktsys.custom_fields
895        with self.env.db_transaction as db:
896            for value in ('foo', 'bar', 'baz', 'blah'):
897                insert_ticket(self.env, reporter='joe',
898                              summary='Summary "%s"' % value,
899                              **{name: value})
900            for name in [name]:
901                quoted[name] = db.quote(name)
902        return quoted
903
904    def test_without_priority_enum(self):
905        quoted = self._setup_no_defined_values_and_custom_field('priority')
906        query = Query.from_string(self.env, 'status!=closed&priority=foo&'
907                                            'priority=blah&order=priority')
908        tickets = self._execute_query(query)
909        self.assertEqual(['Summary "blah"', 'Summary "foo"'],
910                         [t['summary'] for t in tickets])
911        sql, args = query.get_sql(req=self.req)
912        self.assertEqualSQL(sql, """\
913SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,\
914t.type AS type,t.milestone AS milestone,t.time AS time,\
915t.changetime AS changetime,%(priority)s.value AS %(priority)s
916FROM ticket AS t
917  LEFT OUTER JOIN ticket_custom AS %(priority)s ON (%(priority)s.ticket=t.id AND %(priority)s.name='priority')
918WHERE (COALESCE(%(priority)s.value,'') IN (%%s,%%s) AND (COALESCE(t.status,'')!=%%s))
919ORDER BY COALESCE(%(priority)s.value,'')='',%(priority)s.value,t.id""" % quoted)
920        self.assertEqual(['foo', 'blah', 'closed'], args)
921
922    def test_without_resolution_enum(self):
923        quoted = self._setup_no_defined_values_and_custom_field('resolution')
924        query = Query.from_string(self.env, 'status!=closed&resolution=foo&'
925                                            'resolution=blah&order=resolution')
926        tickets = self._execute_query(query)
927        self.assertEqual(['Summary "blah"', 'Summary "foo"'],
928                         [t['summary'] for t in tickets])
929        sql, args = query.get_sql(req=self.req)
930        self.assertEqualSQL(sql, """\
931SELECT t.id AS id,t.summary AS summary,t.status AS status,t.owner AS owner,\
932t.type AS type,t.priority AS priority,t.time AS time,\
933t.changetime AS changetime,priority.value AS _priority_value,\
934%(resolution)s.value AS %(resolution)s
935FROM ticket AS t
936  LEFT OUTER JOIN ticket_custom AS %(resolution)s ON (%(resolution)s.ticket=t.id AND %(resolution)s.name='resolution')
937  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
938WHERE (COALESCE(%(resolution)s.value,'') IN (%%s,%%s) AND (COALESCE(t.status,'')!=%%s))
939ORDER BY COALESCE(%(resolution)s.value,'')='',%(resolution)s.value,t.id""" % quoted)
940        self.assertEqual(['foo', 'blah', 'closed'], args)
941
942    def test_without_type_enum(self):
943        quoted = self._setup_no_defined_values_and_custom_field('type')
944        query = Query.from_string(self.env, 'status!=closed&type=foo&'
945                                            'type=blah&order=type')
946        tickets = self._execute_query(query)
947        self.assertEqual(['Summary "blah"', 'Summary "foo"'],
948                         [t['summary'] for t in tickets])
949        sql, args = query.get_sql(req=self.req)
950        self.assertEqualSQL(sql, """\
951SELECT t.id AS id,t.summary AS summary,t.status AS status,\
952t.owner AS owner,t.priority AS priority,t.milestone AS milestone,\
953t.time AS time,t.changetime AS changetime,\
954priority.value AS _priority_value,%(type)s.value AS %(type)s
955FROM ticket AS t
956  LEFT OUTER JOIN ticket_custom AS %(type)s ON (%(type)s.ticket=t.id AND %(type)s.name='type')
957  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
958WHERE ((COALESCE(t.status,'')!=%%s) AND COALESCE(%(type)s.value,'') IN (%%s,%%s))
959ORDER BY COALESCE(%(type)s.value,'')='',%(type)s.value,t.id""" % quoted)
960        self.assertEqual(['closed', 'foo', 'blah'], args)
961
962    def test_without_milestones(self):
963        quoted = self._setup_no_defined_values_and_custom_field('milestone')
964        query = Query.from_string(self.env, 'status!=closed&milestone=foo&'
965                                            'milestone=blah&order=milestone')
966        tickets = self._execute_query(query)
967        self.assertEqual(['Summary "blah"', 'Summary "foo"'],
968                         [t['summary'] for t in tickets])
969        sql, args = query.get_sql(req=self.req)
970        self.assertEqualSQL(sql, """\
971SELECT t.id AS id,t.summary AS summary,t.status AS status,\
972t.owner AS owner,t.type AS type,t.priority AS priority,\
973t.time AS time,t.changetime AS changetime,\
974priority.value AS _priority_value,%(milestone)s.value AS %(milestone)s
975FROM ticket AS t
976  LEFT OUTER JOIN ticket_custom AS %(milestone)s ON (%(milestone)s.ticket=t.id AND %(milestone)s.name='milestone')
977  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
978WHERE (COALESCE(%(milestone)s.value,'') IN (%%s,%%s) AND (COALESCE(t.status,'')!=%%s))
979ORDER BY COALESCE(%(milestone)s.value,'')='',%(milestone)s.value,t.id""" % quoted)
980        self.assertEqual(['foo', 'blah', 'closed'], args)
981
982    def test_without_versions(self):
983        quoted = self._setup_no_defined_values_and_custom_field('version')
984        query = Query.from_string(self.env, 'status!=closed&version=foo&'
985                                            'version=blah&order=version')
986        tickets = self._execute_query(query)
987        self.assertEqual(['Summary "blah"', 'Summary "foo"'],
988                         [t['summary'] for t in tickets])
989        sql, args = query.get_sql(req=self.req)
990        self.assertEqualSQL(sql, """\
991SELECT t.id AS id,t.summary AS summary,t.status AS status,\
992t.owner AS owner,t.type AS type,t.priority AS priority,\
993t.time AS time,t.changetime AS changetime,priority.value AS _priority_value,\
994%(version)s.value AS %(version)s
995FROM ticket AS t
996  LEFT OUTER JOIN ticket_custom AS %(version)s ON (%(version)s.ticket=t.id AND %(version)s.name='version')
997  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
998WHERE ((COALESCE(t.status,'')!=%%s) AND COALESCE(%(version)s.value,'') IN (%%s,%%s))
999ORDER BY COALESCE(%(version)s.value,'')='',%(version)s.value,t.id""" % quoted)
1000        self.assertEqual(['closed', 'foo', 'blah'], args)
1001
1002    def test_invalid_id_custom_field(self):
1003        self.env.config.set('ticket-custom', 'id', 'text')
1004        ticket = Ticket(self.env)
1005        ticket.populate({'summary': 'test_invalid_id_custom_field',
1006                         'reporter': 'anonymous', 'status': 'new',
1007                         'id': 'blah'})
1008        ticket.insert()
1009        query = Query.from_string(
1010            self.env, 'summary=test_invalid_id_custom_field&col=id')
1011        tickets = self._execute_query(query)
1012        self.assertEqual(ticket.id, tickets[0]['id'])
1013        self.assertEqual('new', tickets[0]['status'])
1014        self.assertEqual('test_invalid_id_custom_field', tickets[0]['summary'])
1015        self.assertEqual(1, len(tickets))
1016
1017    def test_csv_cols_are_labels(self):
1018        self.env.config.set('ticket-custom', 'custom1', 'text')
1019        self.env.config.set('ticket-custom', 'custom1.label', 'CustomOne')
1020        query = Mock(get_columns=lambda: ['id', 'owner', 'milestone',
1021                                          'custom1'],
1022                     execute=lambda r: [{'id': 1,
1023                                         'owner': 'joe@example.org',
1024                                         'milestone': 'milestone1',
1025                                         'custom1': 'val1'}],
1026                     fields=TicketSystem(self.env).get_ticket_fields(),
1027                     time_fields=['time', 'changetime'])
1028        req = Mock(href=self.env.href, perm=MockPerm())
1029        content, mimetype, ext = Mimeview(self.env).convert_content(
1030            req, 'trac.ticket.Query', query, 'csv')
1031        self.assertEqual('\uFEFFid,Owner,Milestone,CustomOne\r\n'
1032                         '1,joe@example.org,milestone1,val1\r\n',
1033                         content.decode('utf-8'))
1034
1035    def test_columns_in_ticket_custom_as_custom_field(self):
1036        fields = ('ticket', 'name', 'value')
1037        for field in fields:
1038            self.env.config.set('ticket-custom', field, 'text')
1039        tktids = []
1040        with self.env.db_transaction as db:
1041            for idx in range(3):
1042                ticket = Ticket(self.env)
1043                ticket.populate({'summary': 'test_ticket_custom_field',
1044                                 'reporter': 'anonymous', 'status': 'new',
1045                                 'ticket': 'ticket-%d' % idx,
1046                                 'name': 'name-%d' % idx,
1047                                 'value': 'value-%d' % idx})
1048                tktid = ticket.insert()
1049                tktids.append(tktid)
1050                if idx == 1:
1051                    db("""DELETE FROM ticket_custom
1052                          WHERE ticket=%s AND name='name'""", (tktid,))
1053                elif idx == 2:
1054                    db("""DELETE FROM ticket_custom
1055                          WHERE ticket=%s AND name='value'""", (tktid,))
1056            quoted = dict((field, db.quote(field)) for field in fields)
1057            quoted['like'] = db.like()
1058        query = Query.from_string(self.env, 'ticket=^ticket-&order=ticket&'
1059                                            'col=summary&col=ticket&col=name&'
1060                                            'col=value')
1061        sql, args = query.get_sql(self.req)
1062        self.assertEqualSQL(sql, """\
1063SELECT t.id AS id,t.summary AS summary,t.status AS status,\
1064t.priority AS priority,t.time AS time,t.changetime AS changetime,\
1065priority.value AS _priority_value,\
1066c.%(ticket)s AS %(ticket)s,c.%(name)s AS %(name)s,c.%(value)s AS %(value)s
1067FROM ticket AS t
1068  LEFT OUTER JOIN (SELECT
1069    ticket AS id,
1070    MAX(CASE WHEN name='ticket' THEN value END) AS %(ticket)s,
1071    MAX(CASE WHEN name='name' THEN value END) AS %(name)s,
1072    MAX(CASE WHEN name='value' THEN value END) AS %(value)s
1073    FROM ticket_custom AS tc
1074    WHERE name IN ('ticket','name','value')
1075    GROUP BY tc.ticket) AS c ON c.id=t.id
1076  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
1077WHERE ((COALESCE(c.%(ticket)s,'') %(like)s))
1078ORDER BY COALESCE(c.%(ticket)s,'')='',c.%(ticket)s,t.id""" % quoted)
1079        tickets = self._execute_query(query)
1080        self.assertEqual(tktids, [t['id'] for t in tickets])
1081        self.assertEqual({'new'}, {t['status'] for t in tickets})
1082        self.assertEqual(['ticket-0', 'ticket-1', 'ticket-2'],
1083                         sorted(t['ticket'] for t in tickets))
1084        self.assertEqual(['', 'name-0', 'name-2'],
1085                         sorted(t['name'] for t in tickets))
1086        self.assertEqual(['', 'value-0', 'value-1'],
1087                         sorted(t['value'] for t in tickets))
1088        self.assertEqual({'test_ticket_custom_field'},
1089                         {t['summary'] for t in tickets})
1090        self.assertEqual(3, len(tickets))
1091
1092    def test_csv_escape(self):
1093        query = Mock(get_columns=lambda: ['id', 'col1'],
1094                     execute=lambda r: [{'id': 1,
1095                                         'col1': 'value, needs escaped'}],
1096                     fields=TicketSystem(self.env).get_ticket_fields(),
1097                     time_fields=['time', 'changetime'])
1098        req = MockRequest(self.env)
1099        content, mimetype, ext = Mimeview(self.env).convert_content(
1100            req, 'trac.ticket.Query', query, 'csv')
1101        self.assertEqual(b'\xef\xbb\xbf'
1102                         b'id,col1\r\n1,"value, needs escaped"\r\n',
1103                         content)
1104
1105    def test_csv_obfuscation(self):
1106        query = Mock(get_columns=lambda: ['id', 'owner', 'reporter', 'cc'],
1107                     execute=lambda r: [{'id': 1,
1108                                         'owner': 'joe@example.org',
1109                                         'reporter': 'foo@example.org',
1110                                         'cc': 'cc1@example.org, cc2'}],
1111                     fields=TicketSystem(self.env).get_ticket_fields(),
1112                     time_fields=['time', 'changetime'])
1113        req = MockRequest(self.env, authname='anonymous')
1114        content, mimetype, ext = Mimeview(self.env).convert_content(
1115            req, 'trac.ticket.Query', query, 'csv')
1116        self.assertEqual('\uFEFFid,Owner,Reporter,Cc\r\n'
1117                         '1,joe@…,foo@…,"cc1@…, cc2"\r\n',
1118                         content.decode('utf-8'))
1119
1120        req = MockRequest(self.env)
1121        content, mimetype, ext = Mimeview(self.env).convert_content(
1122            req, 'trac.ticket.Query', query, 'csv')
1123        self.assertEqual(
1124            '\uFEFFid,Owner,Reporter,Cc\r\n'
1125            '1,joe@example.org,foo@example.org,"cc1@example.org, cc2"\r\n',
1126            content.decode('utf-8'))
1127
1128    def test_template_data(self):
1129        req = MockRequest(self.env)
1130        context = web_context(req, 'query')
1131
1132        query = Query.from_string(self.env, 'owner=$USER&order=id')
1133        tickets = query.execute(req)
1134        data = query.template_data(context, tickets, req=req)
1135        self.assertEqual(['anonymous'], data['clauses'][0]['owner']['values'])
1136
1137        query = Query.from_string(self.env, 'owner=$USER&order=id')
1138        tickets = query.execute(req)
1139        data = query.template_data(context, tickets)
1140        self.assertEqual(['$USER'], data['clauses'][0]['owner']['values'])
1141
1142    def test_properties_script_data(self):
1143        req = MockRequest(self.env, path_info='/query')
1144        template, data = self._process_request(req)
1145        prop = req.chrome['script_data']['properties']['milestone']
1146        self.assertEqual('select', prop['type'])
1147        self.assertEqual('Milestone', prop['label'])
1148        self.assertEqual([], prop['options'])
1149        self.assertEqual([{'label': 'Open (by due date)',
1150                           'options': ['milestone1', 'milestone2']},
1151                          {'label': 'Open (no due date)',
1152                           'options': ['milestone3', 'milestone4']},
1153                          {'label': 'Closed', 'options': []}],
1154                         prop['optgroups'])
1155
1156    def test_properties_script_data_with_no_milestones(self):
1157        self.env.db_transaction("DELETE FROM milestone")
1158        self.env.config.set('ticket-custom', 'milestone', 'text')
1159        req = MockRequest(self.env, path_info='/query')
1160        template, data = self._process_request(req)
1161        prop = req.chrome['script_data']['properties']['milestone']
1162        self.assertEqual({'label': 'Milestone', 'type': 'text',
1163                          'format': 'plain'}, prop)
1164
1165    def test_null_time_and_changetime_with_saved_query_tickets(self):
1166        with self.env.db_transaction as db:
1167            n = self.n_tickets // 2
1168            db("UPDATE ticket SET time=NULL WHERE id<%s", (n,))
1169            db("UPDATE ticket SET changetime=NULL WHERE id>%s", (n,))
1170        req = MockRequest(self.env, path_info='/query', args={'id': '!0'})
1171        self._process_request(req)
1172        self.assertNotEqual('', req.session['query_tickets'])
1173        self._process_request(req)  # TypeError not raised (#12029)
1174
1175    def test_time_fields(self):
1176        when = datetime(2017, 11, 9, 12, 56, 34, 654321, utc)
1177        due = datetime(2017, 9, 18, 12, 34, 56, 876543, utc)
1178        self.env.config.set('ticket-custom', 'due', 'time')
1179        t1 = Ticket(self.env, 1)
1180        t1['due'] = due
1181        t1.save_changes(when=when)
1182        t2 = Ticket(self.env, 2)
1183        t2['due'] = ''  # clear the field
1184        t2.save_changes(when=when + timedelta(seconds=1))
1185        req = MockRequest(self.env, path_info='/query',
1186                          args={'id': '1-3', 'order': 'id', 'col': 'due'})
1187        data = self._process_request(req)[1]
1188        tickets = data['tickets']
1189
1190        t1 = tickets[0]
1191        self.assertEqual('2008-07-01T12:34:56.987654+00:00',
1192                         t1['time'].isoformat())
1193        self.assertEqual('2017-11-09T12:56:34.654321+00:00',
1194                         t1['changetime'].isoformat())
1195        self.assertEqual('2017-09-18T12:34:56.876543+00:00',
1196                         t1['due'].isoformat())
1197
1198        t2 = tickets[1]
1199        self.assertEqual('2008-07-11T12:34:56.987654+00:00',
1200                         t2['time'].isoformat())
1201        self.assertEqual('2017-11-09T12:56:35.654321+00:00',
1202                         t2['changetime'].isoformat())
1203        self.assertIsNone(t2['due'])
1204
1205        t3 = tickets[2]
1206        self.assertEqual('2008-07-21T12:34:56.987654+00:00',
1207                         t3['time'].isoformat())
1208        self.assertEqual('2008-07-22T12:34:56.987654+00:00',
1209                         t3['changetime'].isoformat())
1210        self.assertIsNone(t3['due'])
1211
1212    def test_time_fields_order(self):
1213        dt = datetime(2018, 4, 25, 12, 34, 56, 987654, utc)
1214        self.env.config.set('ticket-custom', 'due', 'time')
1215        tkt = Ticket(self.env, 2)
1216        tkt['due'] = dt
1217        tkt.save_changes(when=dt)
1218
1219        with self.env.db_query as db:
1220            quoted = {'due': db.quote('due')}
1221        query = Query.from_string(self.env, 'col=due&order=due')
1222        sql, args = query.get_sql()
1223        self.assertEqualSQL(sql, """\
1224SELECT t.id AS id,t.status AS status,t.priority AS priority,t.time AS time,\
1225t.changetime AS changetime,priority.value AS _priority_value,\
1226%(due)s.value AS %(due)s
1227FROM ticket AS t
1228  LEFT OUTER JOIN ticket_custom AS %(due)s ON (%(due)s.ticket=t.id AND %(due)s.name='due')
1229  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
1230ORDER BY COALESCE(%(due)s.value,'')='',%(due)s.value,t.id""" % quoted)
1231
1232        req = MockRequest(self.env, path_info='/query',
1233                          args={'id': '1-3', 'order': 'due'})
1234        data = self._process_request(req)[1]
1235        tickets = data['tickets']
1236        self.assertEqual([2, 1, 3], [t['id'] for t in tickets])
1237
1238        req = MockRequest(self.env, path_info='/query',
1239                          args={'id': '1-3', 'order': 'time'})
1240        data = self._process_request(req)[1]
1241        tickets = data['tickets']
1242        self.assertEqual([1, 2, 3], [t['id'] for t in tickets])
1243
1244        req = MockRequest(self.env, path_info='/query',
1245                          args={'id': '1-3', 'order': 'changetime'})
1246        data = self._process_request(req)[1]
1247        tickets = data['tickets']
1248        self.assertEqual([1, 3, 2], [t['id'] for t in tickets])
1249
1250    def test_time_fields_constrained_by_time_range(self):
1251        self.env.config.set('ticket-custom', 'due', 'time')
1252        with self.env.db_transaction as db:
1253            quoted = {'due': db.quote('due')}
1254            tkt = Ticket(self.env, 1)
1255            tkt['due'] = ''
1256            tkt.save_changes()
1257            db("""INSERT INTO ticket_custom (ticket,name,value)
1258                  VALUES (2,'due','blahblah')""")
1259            tkt = Ticket(self.env, 3)
1260            tkt['due'] = datetime(2018, 9, 13, 12, 34, 56, 987654, utc)
1261            tkt.save_changes()
1262            tkt = Ticket(self.env, 4)
1263            tkt['due'] = datetime(2018, 9, 12, 12, 34, 56, 987654, utc)
1264            tkt.save_changes()
1265            tkt = Ticket(self.env, 5)
1266            tkt['due'] = datetime(2018, 9, 11, 12, 34, 56, 987654, utc)
1267            tkt.save_changes()
1268            tkt = Ticket(self.env, 6)
1269            tkt['due'] = datetime(2018, 9, 10, 12, 34, 56, 987654, utc)
1270            tkt.save_changes()
1271
1272        query = Query.from_string(
1273            self.env, 'col=due&due=2018-09-11Z..2018-09-13Z&order=id')
1274        sql, args = query.get_sql()
1275        self.assertEqualSQL(sql, """\
1276SELECT t.id AS id,t.status AS status,t.priority AS priority,t.time AS time,\
1277t.changetime AS changetime,priority.value AS _priority_value,\
1278{due}.value AS {due}
1279FROM ticket AS t
1280  LEFT OUTER JOIN ticket_custom AS {due} ON ({due}.ticket=t.id AND {due}.name='due')
1281  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
1282WHERE ((({due}.value>=%s AND {due}.value<%s)))
1283ORDER BY COALESCE(t.id,0)=0,t.id""".format(**quoted))
1284        self.assertEqual(['001536624000000000', '001536796800000000'], args)
1285
1286        req = MockRequest(self.env, path_info='/query',
1287                          args={'due': '2018-09-11Z..2018-09-13Z',
1288                                'order': 'id', 'desc': '1'})
1289        data = self._process_request(req)[1]
1290        tickets = data['tickets']
1291        self.assertEqual([5, 4], [t['id'] for t in tickets])
1292
1293    def test_time_fields_constrained_by_empty(self):
1294        self.env.config.set('ticket-custom', 'due', 'time')
1295        with self.env.db_transaction as db:
1296            quoted = {'due': db.quote('due')}
1297            tkt = Ticket(self.env, 1)
1298            tkt['due'] = ''
1299            tkt.save_changes()
1300            for tktid in (2, 4, 6):
1301                tkt = Ticket(self.env, tktid)
1302                tkt['due'] = datetime(2018, 9, 8, 12, 34, 56, 987654, utc)
1303                tkt.save_changes()
1304
1305        query = Query.from_string(self.env, 'col=due&id=1-6&due=&order=id')
1306        sql, args = query.get_sql()
1307        self.assertEqualSQL(sql, """\
1308SELECT t.id AS id,t.status AS status,t.priority AS priority,t.time AS time,\
1309t.changetime AS changetime,priority.value AS _priority_value,\
1310{due}.value AS {due}
1311FROM ticket AS t
1312  LEFT OUTER JOIN ticket_custom AS {due} ON ({due}.ticket=t.id AND {due}.name='due')
1313  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
1314WHERE ((COALESCE({due}.value,'')=%s) AND (t.id BETWEEN %s AND %s))
1315ORDER BY COALESCE(t.id,0)=0,t.id""".format(**quoted))
1316        self.assertEqual(['', 1, 6], args)
1317
1318        req = MockRequest(self.env, path_info='/query',
1319                          args={'id': '1-6', 'due': '', 'order': 'id',
1320                                'desc': '1'})
1321        data = self._process_request(req)[1]
1322        tickets = data['tickets']
1323        self.assertEqual([5, 3, 1], [t['id'] for t in tickets])
1324
1325    def test_get_constraints_keep_req_args(self):
1326        arg_list = (('0_type', 'defect'), ('0_type', 'task'),
1327                    ('0_type', 'enhancement'), ('rm_filter_0_type_1', '-'),
1328                    ('1_type', 'task'))
1329        req = MockRequest(self.env, method='POST', path_info='/query',
1330                          arg_list=arg_list)
1331        orig_args = arg_list_to_args(arg_list)
1332        mod = QueryModule(self.env)
1333        self.assertTrue(mod.match_request(req))
1334        template, data = mod.process_request(req)
1335        self.assertEqual([{'type': ['defect', 'enhancement']},
1336                          {'type': ['task']}],
1337                         data['query'].constraints)
1338        self.assertEqual(orig_args, req.args)
1339
1340    def test_checkbox_field(self):
1341        self.env.config.set('ticket-custom', 'blah', 'checkbox')
1342        with self.env.db_transaction as db:
1343            tktids = []
1344            for value in ('1', '0'):
1345                ticket = Ticket(self.env)
1346                ticket.populate({'summary': 'test_ticket_custom_field',
1347                                 'reporter': 'anonymous', 'status': 'new',
1348                                 'blah': value})
1349                tktids.append(ticket.insert())
1350
1351        id_range = '%d-%d' % (tktids[0], tktids[-1])
1352        req = MockRequest(self.env, path_info='/query',
1353                          args={'id': id_range, 'col': 'blah', 'order': 'id'})
1354        mod = QueryModule(self.env)
1355        self.assertTrue(mod.match_request(req))
1356        template, data = mod.process_request(req)
1357        tickets = data['tickets']
1358        self.assertEqual(tktids[0], tickets[0]['id'])
1359        self.assertEqual(True, tickets[0]['blah'])
1360        self.assertEqual(tktids[1], tickets[1]['id'])
1361        self.assertEqual(False, tickets[1]['blah'])
1362        rendered = Chrome(self.env).render_template(req, template, data,
1363                                                    {'fragment': False,
1364                                                     'iterable': False})
1365        rendered = str(rendered, 'utf-8')
1366        matches = list(re.finditer(r'<td class="blah">\s*([^<\s]*)\s*</td>',
1367                                   rendered))
1368        self.assertEqual('yes', matches[0].group(1))
1369        self.assertEqual('no', matches[1].group(1))
1370        self.assertEqual(2, len(matches))
1371
1372        query = Query.from_string(self.env,
1373                                  'id=%s&col=blah&order=id' % id_range)
1374        csv, mimetype, ext = Mimeview(self.env).convert_content(
1375            req, 'trac.ticket.Query', query, 'csv')
1376        self.assertEqual(b'\xef\xbb\xbf'
1377                         b'id,Blah\r\n'
1378                         b'%d,1\r\n'
1379                         b'%d,0\r\n' % (tktids[0], tktids[1]),
1380                         csv)
1381
1382    def test_rss_feed_for_authenticated_users(self):
1383        req = MockRequest(self.env, authname='admin', path_info='/query',
1384                          args={'status': '!closed'})
1385        self._process_request(req)
1386        alternates = req.chrome['links']['alternate']
1387        for link in alternates:
1388            if link['type'] == 'application/rss+xml':
1389                break
1390        else:
1391            self.fail('No application/rss+xml in %r' % alternates)
1392        self.assertIn('/trac.cgi/login?referer=%2Ftrac.cgi%2Fquery%3F',
1393                      link['href'])
1394
1395
1396class QueryLinksTestCase(unittest.TestCase):
1397
1398    def setUp(self):
1399        self.env = EnvironmentStub(default_data=True)
1400        self.query_module = QueryModule(self.env)
1401        self.req = MockRequest(self.env, path_info='/query')
1402        self.context = web_context(self.req)
1403        self.formatter = LinkFormatter(self.env, self.context)
1404
1405    def tearDown(self):
1406        self.env.reset_db()
1407
1408    def _insert_ticket(self, **attrs):
1409        reporter = attrs.pop('reporter', 'joe')
1410        summary = attrs.pop('summary', 'Summary')
1411        status = attrs.pop('status', 'new')
1412        return insert_ticket(self.env, reporter=reporter, summary=summary,
1413                             status=status, **attrs)
1414
1415    def _format_link(self, query, label):
1416        return str(self.query_module._format_link(self.formatter, 'query',
1417                                                  query, label))
1418
1419    def test_empty_query(self):
1420        self.assertEqual('<em class="error">[Error: Query filter requires '
1421                         'field and constraints separated by a "="]</em>',
1422                         self._format_link('', 'label'))
1423
1424    def _process_request(self, query_string):
1425        self.req.arg_list = parse_arg_list(query_string)
1426        self.req.args = arg_list_to_args(self.req.arg_list)
1427        self.assertTrue(self.query_module.match_request(self.req))
1428        template, data = self.query_module.process_request(self.req)
1429        return data
1430
1431    def test_duplicated_order_arguments(self):
1432        data = self._process_request('order=priority&order=id')
1433        self.assertEqual([], data['tickets'])
1434        self.assertEqual('priority', data['query'].order)
1435
1436    def test_duplicated_report_arguments(self):
1437        data = self._process_request('report=1&report=2')
1438        self.assertEqual([], data['tickets'])
1439        self.assertEqual(1, data['query'].id)
1440
1441    def test_duplicated_group_arguments(self):
1442        self._insert_ticket(status='new')
1443        self._insert_ticket(status='assigned')
1444        data = self._process_request(
1445                'group=status&group=status&order=priority')
1446        self.assertNotEqual([], data['tickets'])
1447        self.assertEqual({'new', 'assigned'},
1448                         {t['status'] for t in data['tickets']})
1449        self.assertEqual(2, len(data['tickets']))
1450        self.assertNotEqual([], data['groups'])
1451        self.assertEqual({'new', 'assigned'},
1452                         {value for value, tickets in data['groups']})
1453        self.assertEqual(2, len(data['groups']))
1454
1455    def test_max_only_argument(self):
1456        for _ in range(0, 4):
1457            self._insert_ticket(status='new')
1458        data = self._process_request('max=3')
1459        self.assertEqual(3, data['query'].max)
1460        self.assertEqual(3, len(data['tickets']))
1461
1462    def test_parameter_overrides_default_query(self):
1463        self.env.config.set('query', 'default_anonymous_query',
1464                            'status!=closed&order=milestone'
1465                            'cols=id&cols=summary&cols=status&cols=owner&'
1466                            'group=milestone&max=4&groupdesc=0&desc=1')
1467
1468        data = self._process_request(
1469            'order=status&col=id&col=summary&col=status&col=type&'
1470            'group=status&max=3&groupdesc=1&desc=0')
1471
1472        self.assertEqual('status', data['query'].order)
1473        self.assertEqual(['id', 'summary', 'status', 'type'],
1474                         data['query'].cols)
1475        self.assertEqual('status', data['query'].group)
1476        self.assertEqual(3, data['query'].max)
1477        self.assertTrue(data['query'].groupdesc)
1478        self.assertFalse(data['query'].desc)
1479
1480    def test_non_numeric_report_argument(self):
1481        data = self._process_request('id=0&report=42xx')
1482        self.assertEqual([], data['tickets'])
1483        self.assertEqual(None, data['query'].id)
1484
1485    def test_unicode_report_argument(self):
1486        data = self._process_request(u'id=0&report=42éá')
1487        self.assertEqual([], data['tickets'])
1488        self.assertEqual(None, data['query'].id)
1489
1490
1491class TicketQueryMacroTestCase(unittest.TestCase):
1492
1493    def assertQueryIs(self, content, query, kwargs, format):
1494        qs, kw, f = TicketQueryMacro.parse_args(content)
1495        self.assertEqual(query, qs)
1496        self.assertEqual(kwargs, kw)
1497        self.assertEqual(format, f)
1498
1499    def test_owner_and_milestone(self):
1500        self.assertQueryIs('owner=joe, milestone=milestone1',
1501                           'milestone=milestone1&owner=joe',
1502                           dict(col='status|summary', max='0', order='id'),
1503                           'list')
1504
1505    def test_owner_or_milestone(self):
1506        self.assertQueryIs('owner=joe, or, milestone=milestone1',
1507                           'owner=joe&or&milestone=milestone1',
1508                           dict(col='status|summary', max='0', order='id'),
1509                           'list')
1510
1511    def test_format_arguments(self):
1512        self.assertQueryIs('owner=joe, milestone=milestone1, col=component|severity, max=15, order=component, format=compact',
1513                           'milestone=milestone1&owner=joe',
1514                           dict(col='status|summary|component|severity', max='15', order='component'),
1515                           'compact')
1516        self.assertQueryIs('owner=joe, milestone=milestone1, col=id|summary|component, max=30, order=component, format=table',
1517                           'milestone=milestone1&owner=joe',
1518                           dict(col='id|summary|component', max='30', order='component'),
1519                           'table')
1520
1521    def test_special_char_escaping(self):
1522        self.assertQueryIs(r'owner=joe|jack, milestone=this\&that\|here\,now',
1523                           r'milestone=this\&that\|here,now&owner=joe|jack',
1524                           dict(col='status|summary', max='0', order='id'),
1525                           'list')
1526
1527QUERY_TEST_CASES = """
1528
1529============================== TicketQuery
1530[[TicketQuery]]
1531------------------------------
1532<p>
1533</p><div>\
1534<dl class="wiki compact">\
1535<dt><a class="new" href="/ticket/1" title="This is the summary">#1</a></dt>\
1536<dd>This is the summary</dd>\
1537<dt><a class="assigned" href="/ticket/2" title="This is another summary">#2</a></dt>\
1538<dd>This is another summary</dd>\
1539<dt><a class="closed" href="/ticket/3" title="This is th third summary">#3</a></dt>\
1540<dd>This is th third summary</dd>\
1541</dl>\
1542</div><p>
1543</p>
1544------------------------------
1545============================== TicketQuery()
1546[[TicketQuery()]]
1547------------------------------
1548<p>
1549</p><div>\
1550<dl class="wiki compact">\
1551<dt><a class="new" href="/ticket/1" title="This is the summary">#1</a></dt>\
1552<dd>This is the summary</dd>\
1553<dt><a class="assigned" href="/ticket/2" title="This is another summary">#2</a></dt>\
1554<dd>This is another summary</dd>\
1555<dt><a class="closed" href="/ticket/3" title="This is th third summary">#3</a></dt>\
1556<dd>This is th third summary</dd>\
1557</dl>\
1558</div><p>
1559</p>
1560------------------------------
1561============================== TicketQuery(created=...)
1562[[TicketQuery(created=...)]]
1563------------------------------
1564<p>
1565<div class="system-message"><strong>Macro TicketQuery(created=...) failed</strong><pre>Invalid query constraint value</pre></div>
1566</p>
1567------------------------------
1568============================== TicketQuery(keywords~=x&y)
1569[[TicketQuery(keywords~=x&y)]]
1570------------------------------
1571<p>
1572<div class="system-message"><strong>Macro TicketQuery(keywords~=x&amp;y) failed</strong><pre>Query filter requires field and constraints separated by a "="</pre></div>
1573</p>
1574============================== TicketQuery(format=progress)
1575[[TicketQuery(format=progress)]]
1576------------------------------
1577<p>
1578</p><div class="trac-progress">
1579  <table class="progress">
1580    <tr>
1581      <td class="closed" style="width: 33%">
1582        <a href="/query?status=closed&amp;group=resolution&amp;max=0&amp;order=time" title="1/3 closed"></a>
1583      </td>
1584      <td class="open" style="width: 67%">
1585        <a href="/query?status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id" title="2/3 active"></a>
1586      </td>
1587    </tr>
1588  </table>
1589
1590  <p class="percent">33%</p>
1591
1592  <p class="legend">
1593    <span class="first interval">
1594      <a href="/query?max=0&amp;order=id">Total number of tickets: 3</a>
1595    </span>
1596    <span class="interval">
1597      - <a href="/query?status=closed&amp;group=resolution&amp;max=0&amp;order=time">closed: 1</a>
1598    </span>
1599    <span class="interval">
1600      - <a href="/query?status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id">active: 2</a>
1601    </span>
1602  </p>
1603</div><p>
1604</p>
1605------------------------------
1606============================== TicketQuery(reporter=santa, format=progress)
1607[[TicketQuery(reporter=santa, format=progress)]]
1608------------------------------
1609<p>
1610</p><div class="trac-progress">
1611  <table class="progress">
1612    <tr>
1613      <td class="closed" style="display: none">
1614        <a href="/query?reporter=santa&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time" title="0/1 closed"></a>
1615      </td>
1616      <td class="open" style="width: 100%">
1617        <a href="/query?reporter=santa&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id" title="1/1 active"></a>
1618      </td>
1619    </tr>
1620  </table>
1621
1622  <p class="percent">0%</p>
1623
1624  <p class="legend">
1625    <span class="first interval">
1626      <a href="/query?reporter=santa&amp;max=0&amp;order=id">Total number of tickets: 1</a>
1627    </span>
1628    <span class="interval">
1629      - <a href="/query?reporter=santa&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time">closed: 0</a>
1630    </span>
1631    <span class="interval">
1632      - <a href="/query?reporter=santa&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id">active: 1</a>
1633    </span>
1634  </p>
1635</div><p>
1636</p>
1637------------------------------
1638============================== TicketQuery(reporter=santa&or&owner=santa, format=progress)
1639[[TicketQuery(reporter=santa&or&owner=santa, format=progress)]]
1640------------------------------
1641<p>
1642</p><div class="trac-progress">
1643  <table class="progress">
1644    <tr>
1645      <td class="closed" style="width: 50%">
1646        <a href="/query?reporter=santa&amp;status=closed&amp;or&amp;owner=santa&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time" title="1/2 closed"></a>
1647      </td>
1648      <td class="open" style="width: 50%">
1649        <a href="/query?reporter=santa&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;or&amp;owner=santa&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id" title="1/2 active"></a>
1650      </td>
1651    </tr>
1652  </table>
1653
1654  <p class="percent">50%</p>
1655
1656  <p class="legend">
1657    <span class="first interval">
1658      <a href="/query?reporter=santa&amp;or&amp;owner=santa&amp;max=0&amp;order=id">Total number of tickets: 2</a>
1659    </span>
1660    <span class="interval">
1661      - <a href="/query?reporter=santa&amp;status=closed&amp;or&amp;owner=santa&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time">closed: 1</a>
1662    </span>
1663    <span class="interval">
1664      - <a href="/query?reporter=santa&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;or&amp;owner=santa&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id">active: 1</a>
1665    </span>
1666  </p>
1667</div><p>
1668</p>
1669------------------------------
1670============================== TicketQuery(format=progress, group=project)
1671[[TicketQuery(format=progress, group=project)]]
1672------------------------------
1673<p>
1674</p><div class="trac-groupprogress">
1675  <table>
1676    <tr>
1677      <th scope="row">
1678        <i><a href="/query?project=&amp;max=0&amp;order=id">(none)</a></i>
1679      </th>
1680      <td>
1681
1682  <table class="progress" style="width: 40%">
1683    <tr>
1684      <td class="closed" style="display: none">
1685        <a href="/query?project=&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time" title="0/1 closed"></a>
1686      </td>
1687      <td class="open" style="width: 100%">
1688        <a href="/query?project=&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id" title="1/1 active"></a>
1689      </td>
1690    </tr>
1691  </table>
1692
1693  <p class="percent">0 / 1</p>
1694
1695      </td>
1696    </tr>
1697    <tr>
1698      <th scope="row">
1699        <a href="/query?project=xmas&amp;max=0&amp;order=id">xmas</a>
1700      </th>
1701      <td>
1702
1703  <table class="progress" style="width: 80%">
1704    <tr>
1705      <td class="closed" style="width: 50%">
1706        <a href="/query?project=xmas&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time" title="1/2 closed"></a>
1707      </td>
1708      <td class="open" style="width: 50%">
1709        <a href="/query?project=xmas&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id" title="1/2 active"></a>
1710      </td>
1711    </tr>
1712  </table>
1713
1714  <p class="percent">1 / 2</p>
1715
1716      </td>
1717    </tr>
1718  </table></div><p>
1719</p>
1720------------------------------
1721============================== TicketQuery(format=progress, group=project, groupdesc=1)
1722[[TicketQuery(format=progress, group=project, groupdesc=1)]]
1723------------------------------
1724<p>
1725</p><div class="trac-groupprogress">
1726  <table>
1727    <tr>
1728      <th scope="row">
1729        <a href="/query?project=xmas&amp;max=0&amp;order=id">xmas</a>
1730      </th>
1731      <td>
1732
1733  <table class="progress" style="width: 80%">
1734    <tr>
1735      <td class="closed" style="width: 50%">
1736        <a href="/query?project=xmas&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time" title="1/2 closed"></a>
1737      </td>
1738      <td class="open" style="width: 50%">
1739        <a href="/query?project=xmas&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id" title="1/2 active"></a>
1740      </td>
1741    </tr>
1742  </table>
1743
1744  <p class="percent">1 / 2</p>
1745
1746      </td>
1747    </tr>
1748    <tr>
1749      <th scope="row">
1750        <i><a href="/query?project=&amp;max=0&amp;order=id">(none)</a></i>
1751      </th>
1752      <td>
1753
1754  <table class="progress" style="width: 40%">
1755    <tr>
1756      <td class="closed" style="display: none">
1757        <a href="/query?project=&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time" title="0/1 closed"></a>
1758      </td>
1759      <td class="open" style="width: 100%">
1760        <a href="/query?project=&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id" title="1/1 active"></a>
1761      </td>
1762    </tr>
1763  </table>
1764
1765  <p class="percent">0 / 1</p>
1766
1767      </td>
1768    </tr>
1769  </table></div><p>
1770</p>
1771------------------------------
1772============================== TicketQuery(reporter=santa, format=progress, group=project)
1773[[TicketQuery(reporter=santa, format=progress, group=project)]]
1774------------------------------
1775<p>
1776</p><div class="trac-groupprogress">
1777  <table>
1778    <tr>
1779      <th scope="row">
1780        <a href="/query?project=xmas&amp;reporter=santa&amp;max=0&amp;order=id">xmas</a>
1781      </th>
1782      <td>
1783
1784  <table class="progress" style="width: 80%">
1785    <tr>
1786      <td class="closed" style="display: none">
1787        <a href="/query?project=xmas&amp;reporter=santa&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time" title="0/1 closed"></a>
1788      </td>
1789      <td class="open" style="width: 100%">
1790        <a href="/query?project=xmas&amp;reporter=santa&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id" title="1/1 active"></a>
1791      </td>
1792    </tr>
1793  </table>
1794
1795  <p class="percent">0 / 1</p>
1796
1797      </td>
1798    </tr>
1799  </table></div><p>
1800</p>
1801------------------------------
1802============================== TicketQuery(reporter=santa&or&owner=santa, format=progress, group=project)
1803[[TicketQuery(reporter=santa&or&owner=santa, format=progress, group=project)]]
1804------------------------------
1805<p>
1806</p><div class="trac-groupprogress">
1807  <table>
1808    <tr>
1809      <th scope="row">
1810        <a href="/query?project=xmas&amp;reporter=santa&amp;or&amp;owner=santa&amp;project=xmas&amp;max=0&amp;order=id">xmas</a>
1811      </th>
1812      <td>
1813
1814  <table class="progress" style="width: 80%">
1815    <tr>
1816      <td class="closed" style="width: 50%">
1817        <a href="/query?project=xmas&amp;reporter=santa&amp;status=closed&amp;or&amp;owner=santa&amp;project=xmas&amp;status=closed&amp;group=resolution&amp;max=0&amp;order=time" title="1/2 closed"></a>
1818      </td>
1819      <td class="open" style="width: 50%">
1820        <a href="/query?project=xmas&amp;reporter=santa&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;or&amp;owner=santa&amp;project=xmas&amp;status=accepted&amp;status=assigned&amp;status=new&amp;status=reopened&amp;max=0&amp;order=id" title="1/2 active"></a>
1821      </td>
1822    </tr>
1823  </table>
1824
1825  <p class="percent">1 / 2</p>
1826
1827      </td>
1828    </tr>
1829  </table></div><p>
1830</p>
1831------------------------------
1832"""
1833
1834
1835def ticket_setup(tc):
1836    tc.env.config.set('ticket-custom', 'project', 'text')
1837    insert_ticket(tc.env, reporter='santa', summary='This is the summary',
1838                  status='new', project='xmas')
1839    insert_ticket(tc.env, owner='elf',
1840                  summary='This is another summary', status='assigned')
1841    insert_ticket(tc.env, owner='santa', status='closed',
1842                  summary='This is th third summary', project='xmas')
1843
1844    tc.env.config.set('milestone-groups', 'closed.status', 'closed')
1845    tc.env.config.set('milestone-groups', 'closed.query_args',
1846                      'group=resolution,order=time')
1847    tc.env.config.set('milestone-groups', 'closed.overall_completion', 'true')
1848    tc.env.config.set('milestone-groups', 'active.status', '*')
1849    tc.env.config.set('milestone-groups', 'active.css_class', 'open')
1850
1851
1852def ticket_teardown(tc):
1853    tc.env.reset_db()
1854
1855
1856def test_suite():
1857    suite = unittest.TestSuite()
1858    suite.addTest(unittest.makeSuite(QueryTestCase))
1859    suite.addTest(unittest.makeSuite(QueryLinksTestCase))
1860    suite.addTest(unittest.makeSuite(TicketQueryMacroTestCase))
1861    suite.addTest(formatter.test_suite(QUERY_TEST_CASES, ticket_setup,
1862                                       __file__, ticket_teardown))
1863    return suite
1864
1865if __name__ == '__main__':
1866    unittest.main(defaultTest='test_suite')
1867