예제 #1
0
    def execute_paginated_report(self, req, db, id, sql, args, 
                                 limit=0, offset=0):
        sql, args = self.sql_sub_vars(sql, args, db)
        if not sql:
            raise TracError(_('Report %(num)s has no SQL query.', num=id))
        self.log.debug('Executing report with SQL "%s"' % sql)
        self.log.debug('Request args: %r' % req.args)
        cursor = db.cursor()

        num_items = 0
        if id != -1 and limit > 0:
            # The number of tickets is obtained.
            count_sql = 'SELECT COUNT(*) FROM (' + sql + ') AS tab'
            cursor.execute(count_sql, args)
            self.log.debug("Query SQL(Get num items): " + count_sql)
            for row in cursor:
                pass
            num_items = row[0]
    
            # The column name is obtained.
            get_col_name_sql = 'SELECT * FROM ( ' + sql + ' ) AS tab LIMIT 1'
            cursor.execute(get_col_name_sql, args)
            self.env.log.debug("Query SQL(Get col names): " + get_col_name_sql)
            cols = get_column_names(cursor)

            sort_col = req.args.get('sort', '')
            self.log.debug("Columns %r, Sort column %s" % (cols, sort_col))
            order_cols = []
            if sort_col:
                if '__group__' in cols:
                    order_cols.append('__group__')
                if sort_col in cols:
                    order_cols.append(sort_col)
                else:
                    raise TracError(_('Query parameter "sort=%(sort_col)s" '
                                      ' is invalid', sort_col=sort_col))

            # The report-query results is obtained
            asc = req.args.get('asc', '1')
            asc_str = asc == '1' and 'ASC' or 'DESC'
            order_by = ''
            if len(order_cols) != 0:
                order = ', '.join(order_cols)
                order_by = " ".join([' ORDER BY', order, asc_str])
            sql = " ".join(['SELECT * FROM (', sql, ') AS tab', order_by])
            sql =" ".join([sql, 'LIMIT', str(limit), 'OFFSET', str(offset)])
            self.log.debug("Query SQL: " + sql)
        cursor.execute(sql, args)
        # FIXME: fetchall should probably not be used.
        info = cursor.fetchall() or []
        cols = get_column_names(cursor)

        db.rollback()

        return cols, info, num_items
예제 #2
0
파일: report.py 프로젝트: garym/bloodhound
    def execute_paginated_report(self,
                                 req,
                                 db,
                                 id,
                                 sql,
                                 args,
                                 limit=0,
                                 offset=0):
        sql, args, missing_args = self.sql_sub_vars(sql, args, db)
        if not sql:
            raise TracError(_("Report {%(num)s} has no SQL query.", num=id))

        cursor = db.cursor()

        num_items = 0
        if id != -1 and limit > 0:
            cursor.execute("SELECT COUNT(*) FROM (%s) AS tab" % sql, args)
            num_items = cursor.fetchone()[0]

            # get the column names
            cursor.execute("SELECT * FROM (%s) AS tab LIMIT 1" % sql, args)
            cols = get_column_names(cursor)

            sort_col = req.args.get('sort', '')
            order_cols = []
            if '__group__' in cols:
                sort_col = ''  # sorting is disabled (#15030)
            if sort_col:
                if sort_col in cols:
                    order_cols.append(sort_col)
                else:
                    raise TracError(
                        _('Query parameter "sort=%(sort_col)s" '
                          ' is invalid',
                          sort_col=sort_col))

            # get the (partial) report results
            order_by = ''
            if order_cols:
                asc = req.args.get('asc', '1')
                order_by = " ORDER BY %s %s" % (', '.join(
                    db.quote(col)
                    for col in order_cols), 'ASC' if asc == '1' else 'DESC')
            sql = "SELECT * FROM (%s) AS tab %s LIMIT %s OFFSET %s" % \
                    (sql, order_by, str(limit), str(offset))
            self.log.debug("Query SQL: " + sql)
        cursor.execute(sql, args)
        rows = cursor.fetchall() or []
        cols = get_column_names(cursor)
        return cols, rows, num_items, missing_args
예제 #3
0
    def execute(self, req, db=None):
        if not self.cols:
            self.get_columns()

        sql, args = self.get_sql()
        self.env.log.debug("Query SQL: " + sql % tuple([repr(a)
                                                        for a in args]))

        if not db:
            db = self.env.get_db_cnx()
        cursor = db.cursor()
        cursor.execute(sql, args)
        columns = get_column_names(cursor)
        results = []
        for row in cursor:
            id = int(row[0])
            result = {'id': id, 'href': req.href.ticket(id)}
            for i in range(1, len(columns)):
                name, val = columns[i], row[i]
                if name == self.group:
                    val = val or 'None'
                elif name == 'reporter':
                    val = val or 'anonymous'
                elif name in ['changetime', 'time']:
                    val = int(val)
                elif val is None:
                    val = '--'
                result[name] = val
            results.append(result)
        cursor.close()
        return results
예제 #4
0
    def execute(self, req, db=None):
        if not self.cols:
            self.get_columns()

        sql, args = self.get_sql()
        self.env.log.debug("Query SQL: " + sql % tuple([repr(a) for a in args]))

        if not db:
            db = self.env.get_db_cnx()
        cursor = db.cursor()
        cursor.execute(sql, args)
        columns = get_column_names(cursor)
        results = []
        for row in cursor:
            id = int(row[0])
            result = {'id': id, 'href': req.href.ticket(id)}
            for i in range(1, len(columns)):
                name, val = columns[i], row[i]
                if name == self.group:
                    val = val or 'None'
                elif name == 'reporter':
                    val = val or 'anonymous'
                elif name in ['changetime', 'time']:
                    val = int(val)
                elif val is None:
                    val = '--'
                result[name] = val
            results.append(result)
        cursor.close()
        return results
예제 #5
0
    def execute(self, req, db=None, cached_ids=None):
        if not self.cols:
            self.get_columns()

        sql, args = self.get_sql(req, cached_ids)
        self.env.log.debug("Query SQL: " + sql % tuple([repr(a)
                                                        for a in args]))

        results = []

        for shortname, env in TracForgeAdminSystem(self.env).get_projects():
            # Check that this project matches the constraints, if any
            if 'project' in self.constraints:
                for con in self.constraints['project']:
                    if con.startswith('!'):
                        test = lambda name: name != con[1:]
                    else:
                        test = lambda name: name == con
                    if test(shortname):
                        break
                else:
                    # No constraints matched, skip this env
                    continue

            db = env.get_db_cnx()
            cursor = db.cursor()
            cursor.execute(sql, args)
            columns = get_column_names(cursor)
            fields = []
            for column in columns:
                fields += [f for f in self.fields if f['name'] == column
                           ] or [None]

            for row in cursor:
                id = int(row[0])
                result = {'id': id, 'href': req.href.ticket(id)}
                for i in range(1, len(columns)):
                    name, field, val = columns[i], fields[i], row[i]
                    if name == self.group:
                        val = val or 'None'
                    elif name == 'reporter':
                        val = val or 'anonymous'
                    elif val is None:
                        val = '--'
                    elif name in ('changetime', 'time'):
                        val = datetime.fromtimestamp(int(val), utc)
                    elif field and field['type'] == 'checkbox':
                        try:
                            val = bool(int(val))
                        except TypeError, ValueError:
                            val = False
                    result[name] = val

                    # Add my new data
                    result['project'] = shortname
                    result['href'] = req.href.projects(shortname, 'ticket', id)
                results.append(result)
            cursor.close()
예제 #6
0
    def execute(self, req, db=None, cached_ids=None):
        if not db:
            db = self.env.get_db_cnx()
        cursor = db.cursor()

        sql, args = self.get_sql(req, cached_ids)
        self.num_items = self._count(sql, args, db)

        if self.num_items <= self.max:
            self.has_more_pages = False

        if self.has_more_pages:
            max = self.max
            if self.group:
                max += 1
            sql = sql + " LIMIT %d OFFSET %d" % (max, self.offset)
            if (self.page > int(ceil(float(self.num_items) / self.max)) and
                self.num_items != 0):
                raise TracError(_('Page %(page)s is beyond the number of '
                                  'pages in the query', page=self.page))

        self.env.log.debug("Query SQL: " + sql % tuple([repr(a) for a in args]))     
        try:
            cursor.execute(sql, args)
        except:
            db.rollback()
            raise
        columns = get_column_names(cursor)
        fields = []
        for column in columns:
            fields += [f for f in self.fields if f['name'] == column] or [None]
        results = []

        column_indices = range(len(columns))
        for row in cursor:
            result = {}
            for i in column_indices:
                name, field, val = columns[i], fields[i], row[i]
                if name == self.group:
                    val = val or 'None'
                elif name == 'reporter':
                    val = val or 'anonymous'
                elif name == 'id':
                    val = int(val)
                    result['href'] = req.href.ticket(val)
                elif val is None:
                    val = '--'
                elif name in ('changetime', 'time'):
                    val = datetime.fromtimestamp(int(val or 0), utc)
                elif field and field['type'] == 'checkbox':
                    try:
                        val = bool(int(val))
                    except (TypeError, ValueError):
                        val = False
                result[name] = val
            results.append(result)
        cursor.close()
        return results
예제 #7
0
파일: query.py 프로젝트: nyuhuhuu/trachacks
    def execute(self, req, db=None, cached_ids=None):
        if not self.cols:
            self.get_columns()

        sql, args = self.get_sql(req, cached_ids)
        self.env.log.debug("Query SQL: " + sql % tuple([repr(a) for a in args]))

        results = []
        
        for shortname, env in TracForgeAdminSystem(self.env).get_projects():
            # Check that this project matches the constraints, if any
            if 'project' in self.constraints:
                for con in self.constraints['project']:
                    if con.startswith('!'):
                        test = lambda name: name != con[1:]
                    else:
                        test = lambda name: name == con
                    if test(shortname):
                        break
                else:
                    # No constraints matched, skip this env
                    continue
            
            db = env.get_db_cnx()
            cursor = db.cursor()
            cursor.execute(sql, args)
            columns = get_column_names(cursor)
            fields = []
            for column in columns:
                fields += [f for f in self.fields if f['name'] == column] or [None]

            for row in cursor:
                id = int(row[0])
                result = {'id': id, 'href': req.href.ticket(id)}
                for i in range(1, len(columns)):
                    name, field, val = columns[i], fields[i], row[i]
                    if name == self.group:
                        val = val or 'None'
                    elif name == 'reporter':
                        val = val or 'anonymous'
                    elif val is None:
                        val = '--'
                    elif name in ('changetime', 'time'):
                        val = datetime.fromtimestamp(int(val), utc)
                    elif field and field['type'] == 'checkbox':
                        try:
                            val = bool(int(val))
                        except TypeError, ValueError:
                            val = False
                    result[name] = val
                    
                    # Add my new data
                    result['project'] = shortname
                    result['href'] = req.href.projects(shortname, 'ticket', id)
                results.append(result)
            cursor.close()
예제 #8
0
def upgrade_to_0_2_5(env, db, installed_version):
    if installed_version>=[0,2,5]:
        return True
    db = db or env.get_db_cnx()
    cursor = db.cursor()
    now = to_timestamp(datetime.now(utc))
    cursor.execute("INSERT INTO ticket (type, status, summary, description, time, changetime) "
                       " SELECT '$milestone$', 'new', name, description, %s, %s"
                         " FROM milestone m"
                        " WHERE NOT EXISTS ("
                                " SELECT 1"
                                  " FROM ticket t" 
                                 " WHERE t.type='$milestone$'"
                                   " AND t.summary=m.name)", (now, now))
    cursor.execute("UPDATE ticket"
                     " SET status='finished'"
                   " WHERE status='new'"
                     " AND type='$milestone$'"
                     " AND EXISTS ("
                            " SELECT 1"
                              " FROM milestone m"
                             " WHERE summary=m.name"
                               " AND COALESCE(m.completed,0)<>0)")
    cursor.execute("SELECT * FROM milestone as tab LIMIT 1")
    col_names = get_column_names(cursor)
    if 'started' in col_names:
        cursor.execute("UPDATE ticket"
                         " SET status='started'"
                       " WHERE status='new'"
                         " AND type='$milestone$'"
                         " AND EXISTS ("
                                " SELECT 1"
                                  " FROM milestone m"
                                 " WHERE summary=m.name"
                                   " AND COALESCE(m.started,0)<>0)")
        cursor.execute("INSERT INTO ticket_custom (ticket, name, value)"
                        " SELECT t.id, 'started', m.started"
                          " FROM ticket t, milestone m"
                         " WHERE t.type='$milestone$'"
                           " AND t.summary=m.name"
                           " AND COALESCE(m.started,0)<>0")
                           
        cursor.execute("UPDATE ticket"
                         " SET milestone = (SELECT parent FROM milestone_struct WHERE name=summary)"
                       " WHERE type='$milestone$'")

    trac_cfg = env.config['trac']
    policies = trac_cfg.get('permission_policies') or ''
    for policy in ('CalendarSystem', 'HideMilestoneTicketPolicy'):
        if policies:
            policies = ','.join([policy, policies])
        else:
            policies = policy
    trac_cfg.set('permission_policies', policies)
    env.config.save()
예제 #9
0
    def execute_paginated_report(self, req, db, id, sql, args, 
                                 limit=0, offset=0):
        sql, args, missing_args = self.sql_sub_vars(sql, args, db)
        if not sql:
            raise TracError(_("Report {%(num)s} has no SQL query.", num=id))

        cursor = db.cursor()

        num_items = 0
        if id != -1 and limit > 0:
            cursor.execute("SELECT COUNT(*) FROM (%s) AS tab" % sql, args)
            num_items = cursor.fetchone()[0]
    
            # get the column names
            cursor.execute("SELECT * FROM (%s) AS tab LIMIT 1" % sql, args)
            cols = get_column_names(cursor)

            sort_col = req.args.get('sort', '')
            order_cols = []
            if sort_col:
                if '__group__' in cols:
                    order_cols.append('__group__')
                if sort_col in cols:
                    order_cols.append(sort_col)
                else:
                    raise TracError(_('Query parameter "sort=%(sort_col)s" '
                                      ' is invalid', sort_col=sort_col))

            # get the (partial) report results
            order_by = ''
            if order_cols:
                asc = req.args.get('asc', '1')
                order_by = " ORDER BY %s %s" % (
                       ', '.join(db.quote(col) for col in order_cols),
                        'ASC' if asc == '1' else 'DESC')
            sql = "SELECT * FROM (%s) AS tab %s LIMIT %s OFFSET %s" % \
                    (sql, order_by, str(limit), str(offset))
            self.log.debug("Query SQL: " + sql)
        cursor.execute(sql, args)
        rows = cursor.fetchall() or []
        cols = get_column_names(cursor)
        return cols, rows, num_items, missing_args
예제 #10
0
파일: api.py 프로젝트: nyuhuhuu/trachacks
 def get_ticket_template(self, name):
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     sql = 'SELECT name, fields FROM ticket_templates WHERE name="%s"' % name
     cursor.execute(sql)
     row = cursor.fetchone()
     if row:
         cols = get_column_names(cursor)
         tmpl = dict(zip(cols[0:], row[0:]))
         if tmpl['fields']:
             tmpl['fields'] = tmpl['fields'].split(',')
             fields = [f['name'] for f in TicketSystem(self.env).get_custom_fields()]
             tmpl['fields'] = [f for f in tmpl['fields'] if f in fields]
         else:
             tmpl['fields'] = []
         return tmpl
     else:
         return None
예제 #11
0
파일: api.py 프로젝트: nyuhuhuu/trachacks
 def get_field_groups(self):
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     sql = 'SELECT id AS "name", label, priority AS "order", fields FROM field_groups ORDER BY priority, label ASC'
     groups = []
     cursor.execute(sql)
     rows = cursor.fetchall()
     if not rows:
         return groups
     cols = get_column_names(cursor)
     fields = [f['name'] for f in TicketSystem(self.env).get_custom_fields()]
     for row in rows:
         group = dict(zip(cols[0:], row[0:]))
         group['name'] = 'fieldgroup_'+str(group['name'])
         if group['fields']:
             group['fields'] = group['fields'].split(',')
             group['fields'] = [f for f in group['fields'] if f in fields]
         groups.append(group)
     return groups
예제 #12
0
    def execute_report(self, req, db, id, sql, args):
        sql, args = self.sql_sub_vars(req, sql, args, db)
        if not sql:
            raise TracError('Report %s has no SQL query.' % id)
        if sql.find('__group__') == -1:
            req.hdf['report.sorting.enabled'] = 1

        self.log.debug('Executing report with SQL "%s" (%s)', sql, args)

        cursor = db.cursor()
        cursor.execute(sql, args)

        # FIXME: fetchall should probably not be used.
        info = cursor.fetchall() or []
        cols = get_column_names(cursor)

        db.rollback()

        return cols, info
예제 #13
0
    def execute_report(self, req, db, id, sql, args):
        sql, args = self.sql_sub_vars(req, sql, args, db)
        if not sql:
            raise TracError('Report %s has no SQL query.' % id)
        if sql.find('__group__') == -1:
            req.hdf['report.sorting.enabled'] = 1

        self.log.debug('Executing report with SQL "%s" (%s)', sql, args)

        cursor = db.cursor()
        cursor.execute(sql, args)

        # FIXME: fetchall should probably not be used.
        info = cursor.fetchall() or []
        cols = get_column_names(cursor)

        db.rollback()

        return cols, info
예제 #14
0
파일: api.py 프로젝트: nyuhuhuu/trachacks
 def get_field_group(self, name):
     # names should always be of the form 'fieldgroup_'+id
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     cursor.execute("""
         SELECT id AS "name", label, priority AS "order", fields
         FROM field_groups WHERE id=%s ORDER BY priority, label ASC
         """, (int(name[11:]),))
     row = cursor.fetchone()
     if row:
         cols = get_column_names(cursor)
         group = dict(zip(cols[0:], row[0:]))
         group['name'] = 'fieldgroup_'+str(group['name'])
         if group['fields']:
             group['fields'] = group['fields'].split(',')
             fields = [f['name'] for f in TicketSystem(self.env).get_custom_fields()]
             group['fields'] = [f for f in group['fields'] if f in fields]
         return group
     else:
         return None
예제 #15
0
파일: api.py 프로젝트: nyuhuhuu/trachacks
 def get_ticket_templates(self):
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     sql = 'SELECT name, fields FROM ticket_templates ORDER BY name ASC'
     templates = []
     cursor.execute(sql)
     rows = cursor.fetchall()
     if not rows:
         return templates
     cols = get_column_names(cursor)
     fields = [f['name'] for f in TicketSystem(self.env).get_custom_fields()]
     for row in rows:
         tmpl = dict(zip(cols[0:], row[0:]))
         if tmpl['fields']:
             tmpl['fields'] = tmpl['fields'].split(',')
             tmpl['fields'] = [f for f in tmpl['fields'] if f in fields]
         else:
             tmpl['fields'] = []
         templates.append(tmpl)
     return templates
예제 #16
0
 def get_ticket_template(self, name):
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     sql = 'SELECT name, fields FROM ticket_templates WHERE name="%s"' % name
     cursor.execute(sql)
     row = cursor.fetchone()
     if row:
         cols = get_column_names(cursor)
         tmpl = dict(zip(cols[0:], row[0:]))
         if tmpl['fields']:
             tmpl['fields'] = tmpl['fields'].split(',')
             fields = [
                 f['name']
                 for f in TicketSystem(self.env).get_custom_fields()
             ]
             tmpl['fields'] = [f for f in tmpl['fields'] if f in fields]
         else:
             tmpl['fields'] = []
         return tmpl
     else:
         return None
예제 #17
0
 def get_field_groups(self):
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     sql = 'SELECT id AS "name", label, priority AS "order", fields FROM field_groups ORDER BY priority, label ASC'
     groups = []
     cursor.execute(sql)
     rows = cursor.fetchall()
     if not rows:
         return groups
     cols = get_column_names(cursor)
     fields = [
         f['name'] for f in TicketSystem(self.env).get_custom_fields()
     ]
     for row in rows:
         group = dict(zip(cols[0:], row[0:]))
         group['name'] = 'fieldgroup_' + str(group['name'])
         if group['fields']:
             group['fields'] = group['fields'].split(',')
             group['fields'] = [f for f in group['fields'] if f in fields]
         groups.append(group)
     return groups
예제 #18
0
 def get_ticket_templates(self):
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     sql = 'SELECT name, fields FROM ticket_templates ORDER BY name ASC'
     templates = []
     cursor.execute(sql)
     rows = cursor.fetchall()
     if not rows:
         return templates
     cols = get_column_names(cursor)
     fields = [
         f['name'] for f in TicketSystem(self.env).get_custom_fields()
     ]
     for row in rows:
         tmpl = dict(zip(cols[0:], row[0:]))
         if tmpl['fields']:
             tmpl['fields'] = tmpl['fields'].split(',')
             tmpl['fields'] = [f for f in tmpl['fields'] if f in fields]
         else:
             tmpl['fields'] = []
         templates.append(tmpl)
     return templates
예제 #19
0
 def get_field_group(self, name):
     # names should always be of the form 'fieldgroup_'+id
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     cursor.execute(
         """
         SELECT id AS "name", label, priority AS "order", fields
         FROM field_groups WHERE id=%s ORDER BY priority, label ASC
         """, (int(name[11:]), ))
     row = cursor.fetchone()
     if row:
         cols = get_column_names(cursor)
         group = dict(zip(cols[0:], row[0:]))
         group['name'] = 'fieldgroup_' + str(group['name'])
         if group['fields']:
             group['fields'] = group['fields'].split(',')
             fields = [
                 f['name']
                 for f in TicketSystem(self.env).get_custom_fields()
             ]
             group['fields'] = [f for f in group['fields'] if f in fields]
         return group
     else:
         return None
예제 #20
0
    def execute(self, req=None, db=None, cached_ids=None, authname=None,
                tzinfo=None, href=None, locale=None):
        """Retrieve the list of matching tickets.

        :since 1.0: the `db` parameter is no longer needed and will be removed
        in version 1.1.1
        """
        if req is not None:
            href = req.href
        with self.env.db_direct_query as db:
            cursor = db.cursor()

            self.num_items = 0
            sql, args = self.get_sql(req, cached_ids, authname, tzinfo, locale)
            if sql.startswith('SELECT ') and not sql.startswith('SELECT DISTINCT '):
                sql = 'SELECT DISTINCT * FROM (' + sql + ') AS subquery'
            if isinstance(self.env, ProductEnvironment):
                sql = sql + """ WHERE product='%s'""" % (self.env.product.prefix, )
            self.num_items = self._count(sql, args)

            if self.num_items <= self.max:
                self.has_more_pages = False

            if self.has_more_pages:
                max = self.max
                if self.group:
                    max += 1
                sql = sql + " LIMIT %d OFFSET %d" % (max, self.offset)
                if (self.page > int(ceil(float(self.num_items) / self.max)) and
                    self.num_items != 0):
                    raise TracError(_("Page %(page)s is beyond the number of "
                                      "pages in the query", page=self.page))

            # self.env.log.debug("SQL: " + sql % tuple([repr(a) for a in args]))
            cursor.execute(sql, args)
            columns = get_column_names(cursor)
            fields = []
            for column in columns:
                fields += [f for f in self.fields if f['name'] == column] or \
                          [None]
            results = []

            product_idx = columns.index('product')
            column_indices = range(len(columns))
            for row in cursor:
                result = {}
                for i in column_indices:
                    name, field, val = columns[i], fields[i], row[i]
                    if name == 'reporter':
                        val = val or 'anonymous'
                    elif name == 'id':
                        val = int(val)
                        result['href'] = self._get_ticket_href(
                            row[product_idx], val)
                    elif name in self.time_fields:
                        val = from_utimestamp(val)
                    elif field and field['type'] == 'checkbox':
                        try:
                            val = bool(int(val))
                        except (TypeError, ValueError):
                            val = False
                    elif val is None:
                        val = ''
                    result[name] = val
                results.append(result)
            cursor.close()
            return results
예제 #21
0
파일: report.py 프로젝트: pkdevbox/trac
    def _execute_paginated_report(self, req, db, id, sql, args,
                                  limit=0, offset=0):
        """Deprecated and will be removed in Trac 1.3.1. Call
        `execute_paginated_report` instead."""
        sql, args, missing_args = self.sql_sub_vars(sql, args)
        if not sql:
            raise TracError(_("Report {%(num)s} has no SQL query.", num=id))
        self.log.debug('Report {%d} with SQL "%s"', id, sql)
        self.log.debug('Request args: %r', req.args)

        num_items = 0
        order_by = []
        limit_offset = None
        base_sql = sql.replace(SORT_COLUMN, '1').replace(LIMIT_OFFSET, '')

        cursor = db.cursor()
        if id == self.REPORT_LIST_ID or limit == 0:
            sql = base_sql
        else:
            # The number of tickets is obtained
            count_sql = 'SELECT COUNT(*) FROM (\n%s\n) AS tab' % base_sql
            self.log.debug("Report {%d} SQL (count): %s", id, count_sql)
            try:
                cursor.execute(count_sql, args)
            except Exception as e:
                self.log.warn('Exception caught while executing Report {%d}: '
                              '%r, args %r%s', id, count_sql, args,
                              exception_to_unicode(e, traceback=True))
                return e, count_sql
            num_items = cursor.fetchone()[0]

            # The column names are obtained
            colnames_sql = 'SELECT * FROM (\n%s\n) AS tab LIMIT 1' % base_sql
            self.log.debug("Report {%d} SQL (col names): %s", id, colnames_sql)
            try:
                cursor.execute(colnames_sql, args)
            except Exception as e:
                self.log.warn('Exception caught while executing Report {%d}: '
                              '%r, args %r%s', id, colnames_sql, args,
                              exception_to_unicode(e, traceback=True))
                return e, colnames_sql
            cols = get_column_names(cursor)

            # The ORDER BY columns are inserted
            sort_col = req.args.get('sort', '')
            asc = req.args.get('asc', '1')
            self.log.debug("%r %s (%s)", cols, sort_col, asc and '^' or 'v')
            order_cols = []
            if sort_col and sort_col not in cols:
                raise TracError(_('Query parameter "sort=%(sort_col)s" '
                                  ' is invalid', sort_col=sort_col))
            skel = None
            if '__group__' in cols:
                order_cols.append('__group__')
            if sort_col:
                sort_col = '%s %s' % (db.quote(sort_col),
                                      asc == '1' and 'ASC' or 'DESC')

            if SORT_COLUMN in sql:
                # Method 1: insert sort_col at specified position
                sql = sql.replace(SORT_COLUMN, sort_col or '1')
            elif sort_col:
                # Method 2: automagically insert sort_col (and __group__
                # before it, if __group__ was specified) as first criteria
                if '__group__' in cols:
                    order_by.append('__group__ ASC')
                order_by.append(sort_col)
                # is there already an ORDER BY in the original sql?
                skel = sql_skeleton(sql)
                before, after = split_sql(sql, _order_by_re, skel)
                if after: # there were some other criterions, keep them
                    order_by.append(after)
                sql = ' '.join([before, 'ORDER BY', ', '.join(order_by)])

            # Add LIMIT/OFFSET if pagination needed
            limit_offset = ''
            if num_items > limit:
                limit_offset = ' '.join(['LIMIT', str(limit),
                                         'OFFSET', str(offset)])
            if LIMIT_OFFSET in sql:
                # Method 1: insert LIMIT/OFFSET at specified position
                sql = sql.replace(LIMIT_OFFSET, limit_offset)
            else:
                # Method 2: limit/offset is added unless already present
                skel = skel or sql_skeleton(sql)
                if 'LIMIT' not in skel.upper():
                    sql = ' '.join([sql, limit_offset])
            self.log.debug("Report {%d} SQL (order + limit): %s", id, sql)
        try:
            cursor.execute(sql, args)
        except Exception as e:
            self.log.warn('Exception caught while executing Report {%d}: '
                          '%r, args %r%s', id, sql, args,
                          exception_to_unicode(e, traceback=True))
            if order_by or limit_offset:
                add_notice(req, _("Hint: if the report failed due to automatic"
                                  " modification of the ORDER BY clause or the"
                                  " addition of LIMIT/OFFSET, please look up"
                                  " %(sort_column)s and %(limit_offset)s in"
                                  " TracReports to see how to gain complete"
                                  " control over report rewriting.",
                                  sort_column=SORT_COLUMN,
                                  limit_offset=LIMIT_OFFSET))
            return e, sql
        rows = cursor.fetchall() or []
        cols = get_column_names(cursor)
        return cols, rows, num_items, missing_args, limit_offset
예제 #22
0
            count_sql = "SELECT COUNT(*) FROM (\n%s\n) AS tab" % base_sql
            self.log.debug("Report {%d} SQL (count): %s", id, count_sql)
            try:
                cursor.execute(count_sql, args)
            except Exception, e:
                return e, count_sql
            num_items = cursor.fetchone()[0]

            # The column names are obtained
            colnames_sql = "SELECT * FROM (\n%s\n) AS tab LIMIT 1" % base_sql
            self.log.debug("Report {%d} SQL (col names): %s", id, colnames_sql)
            try:
                cursor.execute(colnames_sql, args)
            except Exception, e:
                return e, colnames_sql
            cols = get_column_names(cursor)

            # The ORDER BY columns are inserted
            sort_col = req.args.get("sort", "")
            asc = req.args.get("asc", "1")
            self.log.debug("%r %s (%s)", cols, sort_col, asc and "^" or "v")
            order_cols = []
            if sort_col and sort_col not in cols:
                raise TracError(_('Query parameter "sort=%(sort_col)s" ' " is invalid", sort_col=sort_col))
            skel = None
            if "__group__" in cols:
                order_cols.append("__group__")
            if sort_col:
                sort_col = "%s %s" % (db.quote(sort_col), asc == "1" and "ASC" or "DESC")

            if SORT_COLUMN in sql:
예제 #23
0
                              '%r, args %r%s', id, count_sql, args,
                              exception_to_unicode(e, traceback=True))
                return e, count_sql
            num_items = cursor.fetchone()[0]

            # The column names are obtained
            colnames_sql = 'SELECT * FROM (\n%s\n) AS tab LIMIT 1' % base_sql
            self.log.debug("Report {%d} SQL (col names): %s", id, colnames_sql)
            try:
                cursor.execute(colnames_sql, args)
            except Exception, e:
                self.log.warn('Exception caught while executing Report {%d}: '
                              '%r, args %r%s', id, colnames_sql, args,
                              exception_to_unicode(e, traceback=True))
                return e, colnames_sql
            cols = get_column_names(cursor)

            # The ORDER BY columns are inserted
            sort_col = req.args.get('sort', '')
            asc = req.args.get('asc', '1')
            self.log.debug("%r %s (%s)", cols, sort_col, asc and '^' or 'v')
            order_cols = []
            if sort_col and sort_col not in cols:
                raise TracError(_('Query parameter "sort=%(sort_col)s" '
                                  ' is invalid', sort_col=sort_col))
            skel = None
            if '__group__' in cols:
                order_cols.append('__group__')
            if sort_col:
                sort_col = '%s %s' % (db.quote(sort_col),
                                      asc == '1' and 'ASC' or 'DESC')
예제 #24
0
파일: report.py 프로젝트: khoi-huynh/trac
    def execute_paginated_report(self, req, id, sql, args, limit=0, offset=0):
        """
        :param req: `Request` object.
        :param id: Integer id of the report.
        :param sql: SQL query that generates the report.
        :param args: SQL query arguments.
        :param limit: Maximum number of results to return (optional).
        :param offset: Offset to start of results (optional).
        """
        sql, args, missing_args = self.sql_sub_vars(sql, args)
        if not sql:
            raise TracError(_("Report {%(num)s} has no SQL query.", num=id))
        self.log.debug('Report {%d} with SQL "%s"', id, sql)
        self.log.debug('Request args: %r', req.args)

        num_items = 0
        order_by = []
        limit_offset = None
        base_sql = sql.replace(SORT_COLUMN, '1').replace(LIMIT_OFFSET, '')

        with self.env.db_query as db:
            cursor = db.cursor()
            if id == self.REPORT_LIST_ID or limit == 0:
                sql = base_sql
            else:
                # The number of tickets is obtained
                count_sql = 'SELECT COUNT(*) FROM (\n%s\n) AS tab' % base_sql
                self.log.debug("Report {%d} SQL (count): %s", id, count_sql)
                try:
                    cursor.execute(count_sql, args)
                except Exception as e:
                    self.log.warning(
                        'Exception caught while executing '
                        'Report {%d}: %r, args %r%s', id, count_sql, args,
                        exception_to_unicode(e, traceback=True))
                    return e, count_sql
                num_items = cursor.fetchone()[0]

                # The column names are obtained
                colnames_sql = 'SELECT * FROM (\n%s\n) AS tab LIMIT 1' \
                               % base_sql
                self.log.debug("Report {%d} SQL (col names): %s", id,
                               colnames_sql)
                try:
                    cursor.execute(colnames_sql, args)
                except Exception as e:
                    self.log.warning(
                        'Exception caught while executing '
                        'Report {%d}: args %r%s', id, colnames_sql, args,
                        exception_to_unicode(e, traceback=True))
                    return e, colnames_sql
                cols = get_column_names(cursor)

                # The ORDER BY columns are inserted
                sort_col = req.args.get('sort', '')
                asc = req.args.getint('asc', 0, min=0, max=1)
                self.log.debug("%r %s (%s)", cols, sort_col,
                               '^' if asc else 'v')
                order_cols = []
                if sort_col and sort_col not in cols:
                    raise TracError(
                        _('Query parameter "sort=%(sort_col)s" '
                          ' is invalid',
                          sort_col=sort_col))
                skel = None
                if '__group__' in cols:
                    order_cols.append('__group__')
                if sort_col:
                    sort_col = '%s %s' % (db.quote(sort_col),
                                          'ASC' if asc else 'DESC')

                if SORT_COLUMN in sql:
                    # Method 1: insert sort_col at specified position
                    sql = sql.replace(SORT_COLUMN, sort_col or '1')
                elif sort_col:
                    # Method 2: automagically insert sort_col (and __group__
                    # before it, if __group__ was specified) as first criteria
                    if '__group__' in cols:
                        order_by.append('__group__ ASC')
                    order_by.append(sort_col)
                    # is there already an ORDER BY in the original sql?
                    skel = sql_skeleton(sql)
                    before, after = split_sql(sql, _order_by_re, skel)
                    if after:  # there were some other criterions, keep them
                        order_by.append(after)
                    sql = ' '.join([before, 'ORDER BY', ', '.join(order_by)])

                # Add LIMIT/OFFSET if pagination needed
                limit_offset = ''
                if num_items > limit:
                    limit_offset = ' '.join(
                        ['LIMIT', str(limit), 'OFFSET',
                         str(offset)])
                if LIMIT_OFFSET in sql:
                    # Method 1: insert LIMIT/OFFSET at specified position
                    sql = sql.replace(LIMIT_OFFSET, limit_offset)
                else:
                    # Method 2: limit/offset is added unless already present
                    skel = skel or sql_skeleton(sql)
                    if 'LIMIT' not in skel.upper():
                        sql = ' '.join([sql, limit_offset])
                self.log.debug("Report {%d} SQL (order + limit): %s", id, sql)
            try:
                cursor.execute(sql, args)
            except Exception as e:
                self.log.warning(
                    'Exception caught while executing Report '
                    '{%d}: %r, args %r%s', id, sql, args,
                    exception_to_unicode(e, traceback=True))
                if order_by or limit_offset:
                    add_notice(
                        req,
                        _(
                            "Hint: if the report failed due to"
                            " automatic modification of the ORDER"
                            " BY clause or the addition of"
                            " LIMIT/OFFSET, please look up"
                            " %(sort_column)s and %(limit_offset)s"
                            " in TracReports to see how to gain"
                            " complete control over report"
                            " rewriting.",
                            sort_column=SORT_COLUMN,
                            limit_offset=LIMIT_OFFSET))
                return e, sql
            rows = cursor.fetchall() or []
            cols = get_column_names(cursor)

        return cols, rows, num_items, missing_args, limit_offset
예제 #25
0
 def intercept_report_rss(self,req,data):
     #figure out which headers are being used
     # - iterate through data's header_groups' list of lists of dictionaries
     # - I'm assuming that each group has identical headers (should always be the case), so I can check only the first header group
     titles = []
     for header in data['header_groups'][0]:
         titles.append(header['col'].strip('_'))
     #Note that "titles" is not used at this point. Because of aliasing in sql, it would be hard to figure out which title means which column
     
     
     #figure out which tickets are listed
     # - iterate through data's row_groups list of lists of dictionaries
     # - I need to check each row group, but the rss feed won't be grouped (it's only time-sorted) so I can then ignore the groupings
     ticket_ids = set()
     ticket_ids.update([row['resource'].id for (_, row_group) in data['row_groups'] for row in row_group])
     self.env.log.debug("Tickets in Report: %s" % ticket_ids)
     
     #generate data based on the headers and the ticket ids
     # - actually, for now I'm just going by the ticket ids because the headers will be tricky: SQL aliasing will make it so I can't just use them verbatim
     #  - oddly enough, the query module will make this work, because it can't alias.
     # - make sure tickets that have been *created* but not *modified* show up in the list
     #  - this is easy with a LEFT JOIN
     #  - COALESCE is used to pick values from the ticket if they don't exist in the change
     # - we're now using our own template, so we don't have to obey report.rss's rules anymore.
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     
     idstring = ','.join([str(s) for s in ticket_ids])
     
     #if their limit is set to '0', sqlite will return 0 rows. thus, make it -1 instead
     limit = self.config.getint('report','items_per_page_rss',-1) or -1
     
     #all the fields starting with 'tc_' will get printed for *all* changes.
     #fields without this prefix will only get printed once for each set of changes.
     sql = """SELECT
         t.summary AS summary,
         t.id AS id,
         t.owner AS owner,
         t.priority AS priority,
         t.milestone AS milestone,
         t.component AS component,
         t.version AS version,
         t.cc AS cc,
         t.keywords AS keywords,
         COALESCE(tc.time, t.time) AS changetime,
         COALESCE(tc.author, t.reporter) AS reporter,
         tc.field AS tc_field,
         tc.oldvalue AS tc_oldvalue,
         tc.newvalue AS tc_newvalue
         FROM ticket t
         LEFT JOIN ticket_change tc ON t.id = tc.ticket
         WHERE t.id IN (%(ids)s)
         ORDER BY changetime ASC
         LIMIT %(limit)s;
         """ % {'ids':idstring,'limit':limit}
     
     cursor.execute(sql)
     
     #convert the rows to dictionaries keyed off of the column names, so we need to find the column names
     cols = get_column_names(cursor)
     #store the rows in a dictionary by ticket so all of one ticket's changes are in one spot.
     # - then store by timestamp in a sub-dictionary so simultaneous changes to a ticket are one feed item
     # - this means that if people make changes to two different tickets simultaneously it won't be an issue
     # - because of the way that the query grabs the data, "title" and "id" will be the same for all changetime-colliding rows
     items = {}
     for row in cursor:
         rowAsDict = dict(zip(cols,row))
         
         if rowAsDict['id'] in items:
             if rowAsDict['changetime'] in items[rowAsDict['id']]:
                 #we've already got something at this time; append it to the changetime's value list
                 items[rowAsDict['id']][rowAsDict['changetime']].append(rowAsDict)
             else:
                 items[rowAsDict['id']][rowAsDict['changetime']] = [rowAsDict]
         else:
             items[rowAsDict['id']] = {rowAsDict['changetime']:[rowAsDict]}
     self.env.log.debug('Tickets in Feed: %s' % items.values())
     #because we're using our own template, we can just blow away the current data structure
     # - keep the report's title, and description, and, uh, report (report is a dictionary that has an id and a resource object pointing to the report)
     # - context lets us use wiki_to_html
     data = {'items':items,
             'title':data['title'],
             'description':data['description'],
             'report':data['report'],
             'context':data['context']}
     
     return ('detailedrss.rss',data,'application/rss+xml')        
예제 #26
0
    def execute_paginated_report(self,
                                 req,
                                 db,
                                 id,
                                 sql,
                                 args,
                                 limit=0,
                                 offset=0):
        sql, args, missing_args = self.sql_sub_vars(sql, args, db)
        if not sql:
            raise TracError(_('Report {%(num)s} has no SQL query.', num=id))
        self.log.debug('Executing report with SQL "%s"' % sql)
        self.log.debug('Request args: %r' % req.args)
        cursor = db.cursor()

        num_items = 0
        if id != -1 and limit > 0:
            # The number of tickets is obtained.
            count_sql = 'SELECT COUNT(*) FROM (' + sql + ') AS tab'
            cursor.execute(count_sql, args)
            self.log.debug("Query SQL(Get num items): " + count_sql)
            for row in cursor:
                pass
            num_items = row[0]

            # The column name is obtained.
            get_col_name_sql = 'SELECT * FROM ( ' + sql + ' ) AS tab LIMIT 1'
            cursor.execute(get_col_name_sql, args)
            self.env.log.debug("Query SQL(Get col names): " + get_col_name_sql)
            cols = get_column_names(cursor)

            sort_col = req.args.get('sort', '')
            self.log.debug("Columns %r, Sort column %s" % (cols, sort_col))
            order_cols = []
            if sort_col:
                if '__group__' in cols:
                    order_cols.append('__group__')
                if sort_col in cols:
                    order_cols.append(sort_col)
                else:
                    raise TracError(
                        _('Query parameter "sort=%(sort_col)s" '
                          ' is invalid',
                          sort_col=sort_col))

            # The report-query results is obtained
            asc = req.args.get('asc', '1')
            asc_str = asc == '1' and 'ASC' or 'DESC'
            order_by = ''
            if len(order_cols) != 0:
                order = ', '.join(db.quote(col) for col in order_cols)
                order_by = " ".join([' ORDER BY', order, asc_str])
            sql = " ".join(['SELECT * FROM (', sql, ') AS tab', order_by])
            sql = " ".join([sql, 'LIMIT', str(limit), 'OFFSET', str(offset)])
            self.log.debug("Query SQL: " + sql)
        cursor.execute(sql, args)
        # FIXME: fetchall should probably not be used.
        info = cursor.fetchall() or []
        cols = get_column_names(cursor)

        db.rollback()

        return cols, info, num_items, missing_args
예제 #27
0
 def intercept_report_rss(self,req,data):
     #figure out which headers are being used
     # - iterate through data's header_groups' list of lists of dictionaries
     # - I'm assuming that each group has identical headers (should always be the case), so I can check only the first header group
     titles = []
     for header in data['header_groups'][0]:
         titles.append(header['col'].strip('_'))
     #Note that "titles" is not used at this point. Because of aliasing in sql, it would be hard to figure out which title means which column
     
     #figure out which tickets are listed
     # - iterate through data's row_groups list of lists of dictionaries
     # - I need to check each row group, but the rss feed won't be grouped (it's only time-sorted) so I can then ignore the groupings
     ticket_ids = set()
     ticket_ids.update([row['resource'].id for (_, row_group) in data['row_groups'] for row in row_group])
     self.env.log.debug("Tickets in Report: %s" % ticket_ids)
     
     #generate data based on the headers and the ticket ids
     # - actually, for now I'm just going by the ticket ids because the headers will be tricky: SQL aliasing will make it so I can't just use them verbatim
     #  - oddly enough, the query module will make this work, because it can't alias.
     # - make sure tickets that have been *created* but not *modified* show up in the list
     #  - this is easy with a LEFT JOIN
     #  - COALESCE is used to pick values from the ticket if they don't exist in the change
     # - we're now using our own template, so we don't have to obey report.rss's rules anymore.
     db = self.env.get_db_cnx()
     cursor = db.cursor()
     
     idstring = ','.join([str(s) for s in ticket_ids])
     
     #if their limit is set to '0', sqlite will return 0 rows. thus, make it -1 instead
     limit = self.config.getint('report','items_per_page_rss',-1) or -1
     
     #all the fields starting with 'tc_' will get printed for *all* changes.
     #fields without this prefix will only get printed once for each set of changes.
     sql = """SELECT
         t.summary AS summary,
         t.id AS id,
         t.owner AS owner,
         t.priority AS priority,
         t.milestone AS milestone,
         t.component AS component,
         t.version AS version,
         t.cc AS cc,
         t.keywords AS keywords,
         COALESCE(tc.time, t.time) AS changetime,
         COALESCE(tc.author, t.reporter) AS reporter,
         tc.field AS tc_field,
         tc.oldvalue AS tc_oldvalue,
         tc.newvalue AS tc_newvalue
         FROM ticket t
         LEFT JOIN ticket_change tc ON t.id = tc.ticket
         WHERE t.id IN (%(ids)s)
         ORDER BY changetime DESC
         LIMIT %(limit)s;
         """ % {'ids':idstring,'limit':limit}
     
     cursor.execute(sql)
     
     #convert the rows to dictionaries keyed off of the column names, so we need to find the column names
     cols = get_column_names(cursor)
     #store the rows in a dictionary by (ticket, timestamp) tuple so simultaneous changes to a ticket are one feed item
     # - this means that if people make changes to two different tickets simultaneously it won't be an issue
     # - because of the way that the query grabs the data, "title" and "id" will be the same for all changetime-colliding rows
     items = {}
     for row in cursor:
         rowAsDict = dict(zip(cols,row))
         
         selector = (rowAsDict['changetime'], rowAsDict['id'])
         if selector in items:
             items[selector].append(rowAsDict)
         else:
             items[selector] = [rowAsDict]
     self.env.log.debug('Tickets in Feed: %s' % items.values())
     #because we're using our own template, we can just blow away the current data structure
     # - keep the report's title, and description, and, uh, report (report is a dictionary that has an id and a resource object pointing to the report)
     # - context lets us use wiki_to_html
     data = {'items':items,
             'title':data['title'],
             'description':data['description'],
             'report':data['report'],
             'context':data['context']}
     
     return ('changesrss.rss',data,'application/rss+xml')