Python Query.get_sql方法代码示例

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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)

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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)

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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))

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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)

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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)

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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)

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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)

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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))

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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)

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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)

# 需要导入模块: from trac.ticket.query import Query [as 别名]
# 或者: from trac.ticket.query.Query import get_sql [as 别名]
    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}