from trac.log import logger_factoryfrom trac.mimeview import Contextfrom trac.test import Mock, EnvironmentStub, MockPermfrom trac.ticket.query import Query, QueryModulefrom trac.web.href import Hreffrom trac.wiki.formatter import LinkFormatterimport unittestimport difflibclass QueryTestCase(unittest.TestCase):    def prettifySQL(self, sql):        """Returns a prettified version of the SQL as a list of lines to help        in creating a useful diff between two SQL statements."""        pretty = []        for line in sql.split('\n'):            pretty.extend([ "%s,\n" % x for x in line.split(',')])        return pretty    def assertEqualSQL(self, sql, correct_sql):        sql_split = self.prettifySQL(sql)        correct_sql_split = self.prettifySQL(correct_sql)        sql_diff = ''.join(list(            difflib.unified_diff(correct_sql_split, sql_split)        ))        failure_message = "%r != %r\n" % (sql, correct_sql) + sql_diff        self.assertEqual(sql, correct_sql, failure_message)    def setUp(self):        self.env = EnvironmentStub(default_data=True)        self.req = Mock(href=self.env.href, authname='anonymous')            def test_all_ordered_by_id(self):        query = Query(self.env, order='id')        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)ORDER BY COALESCE(t.id,0)=0,t.id""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_all_ordered_by_id_desc(self):        query = Query(self.env, order='id', desc=1)        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)ORDER BY COALESCE(t.id,0)=0 DESC,t.id DESC""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_all_ordered_by_id_verbose(self):        query = Query(self.env, order='id', verbose=1)        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.reporter AS reporter,t.description AS description,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)ORDER BY COALESCE(t.id,0)=0,t.id""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_all_ordered_by_priority(self):        query = Query(self.env) # priority is default order        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)ORDER BY COALESCE(t.priority,'')='',priority.value,t.id""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_all_ordered_by_priority_desc(self):        query = Query(self.env, desc=1) # priority is default order        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)ORDER BY COALESCE(t.priority,'')='' DESC,priority.value DESC,t.id""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_all_ordered_by_version(self):        query = Query(self.env, order='version')        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)  LEFT OUTER JOIN version ON (version.name=version)ORDER BY COALESCE(t.version,'')='',COALESCE(version.time,0)=0,version.time,t.version,t.id""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_all_ordered_by_version_desc(self):        query = Query(self.env, order='version', desc=1)        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)  LEFT OUTER JOIN version ON (version.name=version)ORDER BY COALESCE(t.version,'')='' DESC,COALESCE(version.time,0)=0 DESC,version.time DESC,t.version DESC,t.id""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_constrained_by_milestone(self):        query = Query.from_string(self.env, 'milestone=milestone1', order='id')        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)WHERE COALESCE(t.milestone,'')=%sORDER BY COALESCE(t.id,0)=0,t.id""")        self.assertEqual(['milestone1'], args)        tickets = query.execute(self.req)    def test_all_grouped_by_milestone(self):        query = Query(self.env, order='id', group='milestone')        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)  LEFT OUTER JOIN milestone ON (milestone.name=milestone)ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(t.id,0)=0,t.id""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_all_grouped_by_milestone_desc(self):        query = Query(self.env, order='id', group='milestone', groupdesc=1)        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)  LEFT OUTER JOIN milestone ON (milestone.name=milestone)ORDER BY COALESCE(t.milestone,'')='' DESC,COALESCE(milestone.due,0)=0 DESC,milestone.due DESC,t.milestone DESC,COALESCE(t.id,0)=0,t.id""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_grouped_by_priority(self):        query = Query(self.env, group='priority')        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.milestone AS milestone,t.component AS component,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)ORDER BY COALESCE(t.priority,'')='',priority.value,t.id""")        self.assertEqual([], args)        tickets = query.execute(self.req)    def test_constrained_by_milestone_not(self):        query = Query.from_string(self.env, 'milestone!=milestone1', order='id')        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""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_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)WHERE COALESCE(t.milestone,'')!=%sORDER BY COALESCE(t.id,0)=0,t.id""")        self.assertEqual(['milestone1'], args)        tickets = query.execute(self.req)    def test_constrained_by_status(self):        query = Query.from_string(self.env, 'status=new|assigned|reopened',                                  order='id')        sql, args = query.get_sql()        self.assertEqualSQL(sql,"""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_valueFROM ticket AS t  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)WHERE COALESCE(t.status,'') IN (%s,%s,%s)ORDER BY COALESCE(t.id,0)=0,t.id""")        self.assertEqual(['new', 'assigned', 'reopened'], args)        tickets = query.execute(self.req)