def test_all_ordered_by_id_verbose(self): query = Query(self.env, order='id', verbose=1) sql = query.get_sql() self.assertEqual(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.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""") tickets = query.execute()
def test_all_ordered_by_priority_desc(self): query = Query(self.env, desc=1) # priority is default order sql = query.get_sql() self.assertEqual(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.priority,'')='' DESC,priority.value DESC,t.id""") tickets = query.execute()
def test_grouped_by_priority(self): query = Query(self.env, group='priority') sql = query.get_sql() self.assertEqual(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.milestone AS milestone,t.component AS component,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.priority,'')='',priority.value,t.id""") tickets = query.execute()
def test_all_ordered_by_id_desc(self): query = Query(self.env, order='id', desc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.product AS product,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.id,0)=0 DESC,t.id DESC""") self.assertEqual([], args) tickets = query.execute(self.req)
def test_all_ordered_by_version_desc(self): query = Query(self.env, order='version', desc=1) sql = query.get_sql() self.assertEqual(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.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""") tickets = query.execute()
def test_all_grouped_by_milestone_desc(self): query = Query(self.env, order='id', group='milestone', groupdesc=1) sql = query.get_sql() self.assertEqual(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.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.due,0)=0 DESC,milestone.due DESC,t.milestone DESC,COALESCE(t.id,0)=0,t.id""") tickets = query.execute()
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.assertEqual(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(t.priority,'')='' DESC,priority.value DESC,t.id""") self.assertEqual([], args) tickets = query.execute(Mock(href=self.env.href))
def test_grouped_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query(self.env, group='foo', order='id') sql = query.get_sql() self.assertEqual(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,foo.value AS foo FROM ticket AS t LEFT OUTER JOIN ticket_custom AS foo ON (id=foo.ticket AND foo.name='foo') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(foo.value,'')='',foo.value,COALESCE(t.id,0)=0,t.id""") tickets = query.execute()
def test_all_ordered_by_priority_desc(self): query = Query(self.env, desc=1) # priority is default order sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='' DESC,%(cast_priority)s DESC,t.id""" % { 'cast_priority': self.env.get_db_cnx().cast('priority.value', 'int')}) self.assertEqual([], args) tickets = query.execute(self.req)
def test_all_grouped_by_milestone_desc(self): query = Query(self.env, order='id', group='milestone', groupdesc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.product AS product,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN milestone ON (milestone.name=milestone) ORDER BY COALESCE(t.milestone,'')='' DESC,COALESCE(milestone.completed,0)=0 DESC,milestone.completed DESC,COALESCE(milestone.due,0)=0 DESC,milestone.due DESC,t.milestone DESC,COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(self.req)
def test_all_ordered_by_version_desc(self): query = Query(self.env, order='version', desc=1) sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) LEFT OUTER JOIN version ON (version.name=version) ORDER BY COALESCE(t.version,'')='' DESC,COALESCE(version.time,0)=0 DESC,version.time DESC,t.version DESC,t.id""") self.assertEqual([], args) tickets = query.execute(self.req)
def test_grouped_by_priority(self): query = Query(self.env, group='priority') sql, args = query.get_sql() self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.product AS product,t.milestone AS milestone,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % { 'cast_priority': self.env.get_read_db().cast('priority.value', 'int')}) self.assertEqual([], args) tickets = query.execute(self.req)
def test_all_ordered_by_id(self): query = Query(self.env, order='id') sql, args = query.get_sql() self.assertEqual( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value 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(Mock(href=self.env.href))
def test_grouped_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query(self.env, group='foo', order='id') sql, args = query.get_sql() self.assertEqual(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,foo.value AS foo FROM ticket AS t LEFT OUTER JOIN ticket_custom AS foo ON (id=foo.ticket AND foo.name='foo') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(foo.value,'')='',foo.value,COALESCE(t.id,0)=0,t.id""") self.assertEqual([], args) tickets = query.execute(Mock(href=self.env.href))
def test_grouped_by_custom_field(self): self.env.config.set('ticket-custom', 'foo', 'text') query = Query(self.env, group='foo', order='id') sql, args = query.get_sql() foo = self.env.get_read_db().quote('foo') self.assertEqualSQL(sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.product AS product,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,%s.value AS %s FROM ticket AS t LEFT OUTER JOIN ticket_custom AS %s ON (id=%s.ticket AND %s.name='foo') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(%s.value,'')='',%s.value,COALESCE(t.id,0)=0,t.id""" % ((foo,) * 7)) self.assertEqual([], args) tickets = query.execute(self.req)
def test_all_ordered_by_priority_desc(self): query = Query(self.env, desc=1) # priority is default order sql, args = query.get_sql() self.assertEqualSQL( sql, """SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value FROM ticket AS t LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(priority.value,'')='' DESC,%(cast_priority)s DESC,t.id""" % { 'cast_priority': self.env.get_db_cnx().cast('priority.value', 'int') }) self.assertEqual([], args) tickets = query.execute(self.req)
def test_grouped_by_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,c.%s AS %s FROM ticket AS t LEFT JOIN (SELECT id AS ticket, (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='foo') AS %s FROM ticket t) AS c ON (c.ticket=t.id) LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(c.%s,'')='',c.%s,COALESCE(t.id,0)=0,t.id""" % ((foo,) * 5)) 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,c.%s AS %s FROM ticket AS t LEFT JOIN (SELECT id AS ticket, (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='foo') AS %s FROM ticket t) AS c ON (c.ticket=t.id) LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) ORDER BY COALESCE(c.%s,'')='',c.%s,COALESCE(t.id,0)=0,t.id""" % ((foo,) * 5), ) self.assertEqual([], args) tickets = query.execute(self.req)
def _make_data(self,req,opts): arg_x_min = opts['start'] arg_x_max = opts['end'] per = opts['per'] query_str = opts['query'] fieldlist = opts['col'] upper = opts['upper'] if len(query_str)>1 and query_str[0]!='?': query_str = query_str + ''.join(['&col=%s'%field for field in fieldlist]) query = Query.from_string(self.env, query_str) elif len(query_str)>1 and query_str[0]=='?': constraints = self._get_constraints(self._urlstring_to_reqarg(query_str)) query = Query(self.env, constraints=constraints, cols=fieldlist) else: constraints = self._get_constraints(req.args) query = Query(self.env, constraints=constraints, cols=fieldlist) self.log.debug(query.get_sql()) result = query.execute(req, db=None, cached_ids=None) result_len = len(result) daylists = [] edgedays = [] for fieldname in fieldlist: daylist =[] dayids = {} for ticket in result: try: dtin = ticket[fieldname] # parse date if isinstance(dtin, (str,unicode)): dt = parse_date(dtin) else: dt = to_datetime(dtin) except: continue d = datetime(dt.year,dt.month,dt.day,tzinfo=req.tz) daylist.append(d) daylists.append( (daylist,dayids) ) if len(daylist)>0 : edgedays.append(min(daylist)) edgedays.append(max(daylist)) if len(edgedays)==0: return None#'''No data to output.''' x_min = arg_x_min or min(edgedays) x_max = arg_x_max or max(edgedays) x_min = datetime(x_min.year,x_min.month,x_min.day,tzinfo=x_min.tzinfo) x_max = datetime(x_max.year,x_max.month,x_max.day,tzinfo=x_max.tzinfo) if per=='week': x_axis = [x_min+timedelta(x) for x in range(0,(x_max-x_min).days+7,7)] x_max = x_axis[-1] # As x_axis[-1] may be larger than x_max. else: #'day','free' x_axis = [x_min+timedelta(x) for x in range(0,(x_max-x_min).days+1)] counts={} linenum = 0 for daylist,dayids in daylists: for x in x_axis: count = len([1 for c in daylist if c <= x]) #ids = [dayids[c] for c in daylist if c <= x and c>last_x ] #points.append(Point(x,count)) if not upper: count = result_len - count counts[(x,linenum)] = count linenum += 1 return {'x_axis':x_axis, 'counts':counts, 'linenum':linenum, 'x_min':x_min, 'x_max':x_max, 'fieldlist':fieldlist, 'result_len':result_len}