def test_all_ordered_by_id_verbose(self): query = Query(self.env, order='id', verbose=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.reporter AS reporter,t.description AS description,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req)
def test_all_ordered_by_priority_desc(self): query = Query(self.env, desc=1) # priority is default order sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='' DESC,%(cast_priority)s DESC,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int')}) self.assertEqual([], args) tickets = query.execute(self.req)
def test_all_grouped_by_milestone_desc(self): query = Query(self.env, order='id', group='milestone', groupdesc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.component AS component,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='' DESC,COALESCE(milestone.completed,0)=0 DESC,milestone.completed DESC,COALESCE(milestone.due,0)=0 DESC,milestone.due DESC,t.milestone DESC,COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req)
def test_all_ordered_by_version_desc(self): query = Query(self.env, order='version', desc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='' DESC,COALESCE(version.time,0)=0 DESC,version.time DESC,t.version DESC,t.id""") self.assertEqual([], args) tickets = query.execute(self.req)
def test_grouped_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query(self.env, group='foo', order='id') sql, args = query.get_sql() foo = self.env.get_read_db().quote('foo') self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,%s.value AS %s FROM ticket AS t LEFT OUTER JOIN ticket_custom AS %s ON (id=%s.ticket AND %s.name='foo') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(%s.value,'')='',%s.value,COALESCE(t.id,0)=0,t.id""" % ((foo,) * 7)) self.assertEqual([], args) tickets = query.execute(self.req)