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}
