def _from_database(self, row):
     name, due, completed, description = row
     self.name = name
     self.due = from_utimestamp(due) if due else None
     self.completed = from_utimestamp(completed) if completed else None
     self.description = description or ''
     self._to_old()

 def _from_database(self, row):
     name, due, completed, description = row
     self.name = name
     self.due = due and from_utimestamp(due) or None
     self.completed = completed and from_utimestamp(completed) or None
     self.description = description or ''
     self._to_old()

 def _fetch_ticket(self):
     rts = RemoteTicketSystem(self.env)
     db = self.env.get_read_db()
     cursor = db.cursor()
     
     # Try to retrieve remote ticket from cache
     cursor.execute('''SELECT %s FROM remote_tickets 
                    WHERE remote_name=%%s and id=%%s
                    ''' % (', '.join(self.table_fields)),
                    (self.remote_name, self.id))
     row = cursor.fetchone()
     
     # Remote ticket not in cache
     if not row:
         self._refresh_ticket()
     
     self._cachetime = from_utimestamp(row[self.cachetime_pos])
     ttl = timedelta(seconds=int(rts.cache_ttl) // 1000, 
                     microseconds=int(rts.cache_ttl) % 1000 * 1000)
     
     # Cached remote ticket is too old
     if self._cachetime < datetime.now(utc) - ttl:
         self._refresh_ticket()
     
     # Cached ticket is valid, populate instance
     for name, value in zip(self.remote_fields, row):
         if name in self.time_fields:
             self.values[name] = from_utimestamp(value)
         elif value is None:
             self.values[name] = empty
         else:
             self.values[name] = value

 def get_comment_history(self, cnum, db=None):
     db = self._get_db(db)
     history = []
     cursor = db.cursor()
     row = self._find_change(cnum, db)
     if row:
         ts0, author0, last_comment = row
         # Get all fields of the form "_comment%d"
         cursor.execute("""
             SELECT field,author,oldvalue,newvalue 
             FROM ticket_change 
             WHERE ticket=%%s AND time=%%s AND field %s
             """ % db.like(), (self.id, ts0,
                               db.like_escape('_comment') + '%'))
         rows = sorted((int(field[8:]), author, old, new)
                       for field, author, old, new in cursor)
         for rev, author, comment, ts in rows:
             history.append((rev, from_utimestamp(long(ts0)), author0,
                             comment))
             ts0, author0 = ts, author
         history.sort()
         rev = history and (history[-1][0] + 1) or 0
         history.append((rev, from_utimestamp(long(ts0)), author0,
                         last_comment))
     return history

    def get_timeline_events(self, req, start, stop, filters):
        if ('sensitive_activity' in filters and
            'SENSITIVE_ACTIVITY_VIEW' in req.perm and
            'SENSITIVE_VIEW' not in req.perm):
            ts_start = to_utimestamp(start)
            ts_stop = to_utimestamp(stop)

            db = self.env.get_db_cnx()
            cursor = db.cursor()

            if 'ticket_details' in filters:
                # only show sensitive ticket changes (edits, closure) if the 'ticket_details' filter is on:
                cursor.execute("""
                    SELECT DISTINCT t.id,tc.time,tc.oldvalue
                    FROM ticket_change tc 
                        INNER JOIN ticket t ON t.id = tc.ticket 
                            AND tc.time >= %s AND tc.time <= %s  AND tc.field = %s
                        INNER JOIN ticket_custom td ON t.id = td.ticket
                            AND td.name = %s AND td.value = %s
                    ORDER BY tc.time
                    """, (ts_start, ts_stop, 'comment', 'sensitive', '1'))
                for tid,t,cid in cursor:
                    yield ('sensitive_activity', from_utimestamp(t), 'redacted', (tid, cid))
            # always show new sensitive tickets:
            cursor.execute('''
               SELECT DISTINCT id, time FROM
                  ticket t INNER JOIN ticket_custom tc ON t.id = tc.ticket
                   AND t.time >= %s AND t.time <= %s
                   AND tc.name = %s AND tc.value = %s
               ORDER BY time
               ''', (ts_start, ts_stop, 'sensitive', '1'))
            for tid,t in cursor:
                yield ('sensitive_activity', from_utimestamp(t), 'redacted', (tid, None))

 def formatter(self, col, cell_value):
     if col == 'time':
         return cell_value != '' and format_time(from_utimestamp(long(cell_value))) or '--'
     if col in ('date', 'created', 'modified'):
         return cell_value != '' and format_date(from_utimestamp(long(cell_value))) or '--'
     if col == 'datetime':
         return cell_value != '' and format_datetime(from_utimestamp(long(cell_value))) or '--'
     return cell_value

    def render_admin_panel(self, req, cat, page, path_info):
        req.perm.require('SVNVERIFY_REPORT')
        
        rm = RepositoryManager(self.env)
        all_repos = rm.get_all_repositories()
        db = self.env.get_read_db()
        cursor = db.cursor()
        
        if path_info:
            # detailed
            reponame = not is_default(path_info) and path_info or ''
            info = all_repos.get(reponame)
            if info is None:
                raise TracError(_("Repository '%(repo)s' not found",
                                  repo=path_info))

            cursor.execute("SELECT type, time, result, log "
                           "FROM svnverify_log WHERE repository_id = %s "
                           "ORDER BY time DESC LIMIT 1",
                           (info['id'],))
            row = cursor.fetchone()
            if row:
                info['check_type'] = row[0]
                info['time_checked'] = format_datetime(from_utimestamp(row[1]))
                info['pretty_status'] = int(row[2]) == 0 and "OK" or "Warning"
                info['status'] = row[2]
                info['log'] = row[3]
            info['prettydir'] = breakable_path(info['dir'])
            if info['name'] == '':
                info['name'] = "(default)"
            return 'svnverify.html', {"info": info}
        else:
            repositories = {}
            for reponame, info in all_repos.iteritems():
                if info.get('type',rm.repository_type) == "svn" or (rm.repository_type == 'svn' and info.get('type') == ''):
                    info['prettydir'] = breakable_path(info['dir'])
                    try:
                        r = RepositoryManager(self.env).get_repository(reponame)
                        info['rev'] = r.get_youngest_rev()
                        info['display_rev'] = r.display_rev(info['rev'])
                    except:
                        pass
                    cursor.execute("SELECT type, time, result "
                                   "FROM svnverify_log "
                                   "WHERE repository_id = %s "
                                   "ORDER BY time DESC LIMIT 1",
                                   (info['id'],))
                    row = cursor.fetchone()
                    if row:
                        info['check_type'] = row[0]
                        info['time_checked'] = format_datetime(from_utimestamp(row[1]))
                        info['pretty_status'] = int(row[2]) == 0 and "OK" or "Warning"
                        info['status'] = row[2]

                    repositories[reponame] = info

            add_stylesheet(req, 'svnverify/css/svnverify.css')
            return 'svnverifylist.html', {"repositories": repositories}

    def get_comment_history(self, cnum=None, cdate=None, db=None):
        """Retrieve the edit history of a comment identified by its number or
        date.

        :since 1.0: the `db` parameter is no longer needed and will be removed
        in version 1.1.1
        """
        if cdate is None:
            row = self._find_change(cnum)
            if not row:
                return
            ts0, author0, last_comment = row
        else:
            ts0, author0, last_comment = to_utimestamp(cdate), None, None
        with self.env.db_query as db:
            # Get last comment and author if not available
            if last_comment is None:
                last_comment = ""
                for author0, last_comment in db(
                    """
                        SELECT author, newvalue FROM ticket_change
                        WHERE ticket=%s AND time=%s AND field='comment'
                        """,
                    (self.id, ts0),
                ):
                    break
            if author0 is None:
                for author0, last_comment in db(
                    """
                        SELECT author, newvalue FROM ticket_change
                        WHERE ticket=%%s AND time=%%s AND NOT field %s LIMIT 1
                        """
                    % db.like(),
                    (self.id, ts0, db.like_escape("_") + "%"),
                ):
                    break
                else:
                    return

            # Get all fields of the form "_comment%d"
            rows = db(
                """SELECT field, author, oldvalue, newvalue
                         FROM ticket_change
                         WHERE ticket=%%s AND time=%%s AND field %s
                         """
                % db.like(),
                (self.id, ts0, db.like_escape("_comment") + "%"),
            )
            rows = sorted((int(field[8:]), author, old, new) for field, author, old, new in rows)
            history = []
            for rev, author, comment, ts in rows:
                history.append((rev, from_utimestamp(long(ts0)), author0, comment))
                ts0, author0 = ts, author
            history.sort()
            rev = history[-1][0] + 1 if history else 0
            history.append((rev, from_utimestamp(long(ts0)), author0, last_comment))
            return history

def parse_options(env, content, options):
    """Parses the parameters, makes some sanity checks, and creates default values
    for missing parameters.
    """
    _, parsed_options = parse_args(content, strict=False)

    options.update(parsed_options)
    today = datetime.now().date()

    startdatearg = options.get('startdate')
    if startdatearg:
        options['startdate'] = \
            datetime(*strptime(startdatearg, "%Y-%m-%d")[0:5]).date()

    enddatearg = options.get('enddate')
    options['enddate'] = None
    if enddatearg:
        options['enddate'] = \
            datetime(*strptime(enddatearg, "%Y-%m-%d")[0:5]).date()

    if not options['enddate'] and options.get('milestone'):
        # use first milestone
        milestone = options['milestone'].split('|')[0]
        # try to get end date from db
        for completed, due in env.db_query("""
                SELECT completed, due FROM milestone WHERE name = %s
                """, (milestone,)):
            if completed:
                options['enddate'] = from_utimestamp(completed).date()
            elif due:
                due = from_utimestamp(due).date()
                if due >= today:
                    options['enddate'] = due
            break
        else:
            raise TracError("Couldn't find milestone %s" % milestone)

    options['enddate'] = options['enddate'] or today
    options['today'] = options.get('today') or today

    if options.get('weekends'):
        options['weekends'] = parse_bool(options['weekends'])

    if options.get('spent'):
        options['spent'] = parse_bool(options['spent'] )

    # all arguments that are no key should be treated as part of the query
    query_args = {}
    for key in options.keys():
        if key not in AVAILABLE_OPTIONS:
            query_args[key] = options[key]
    return options, query_args

    def process_request(self, req):
        id = int(req.args.get('id'))
        req.perm('ticket', id).require('TICKET_ADMIN')
        ticket = Ticket(self.env, id)
        action = req.args['action']
        cnum = req.args.get('cnum')
        if req.method == 'POST':
            if 'cancel' in req.args:
                href = req.href.ticket(id)
                if action == 'delete-comment':
                    href += '#comment:%s' % cnum
                req.redirect(href)

            if action == 'delete':
                ticket.delete()
                add_notice(req, _('The ticket #%(id)s has been deleted.',
                                  id=ticket.id))
                req.redirect(req.href())

            elif action == 'delete-comment':
                cdate = from_utimestamp(long(req.args.get('cdate')))
                ticket.delete_change(cdate=cdate)
                add_notice(req, _('The ticket comment %(num)s on ticket '
                                  '#%(id)s has been deleted.',
                                  num=cnum, id=ticket.id))
                req.redirect(req.href.ticket(id))

        tm = TicketModule(self.env)
        data = tm._prepare_data(req, ticket)
        tm._insert_ticket_data(req, ticket, data,
                               get_reporter_id(req, 'author'), {})
        data.update(action=action, cdate=None)

        if action == 'delete-comment':
            data['cdate'] = req.args.get('cdate')
            cdate = from_utimestamp(long(data['cdate']))
            for change in data['changes']:
                if change.get('date') == cdate:
                    data['change'] = change
                    data['cnum'] = change.get('cnum')
                    break
            else:
                raise TracError(_('Comment %(num)s not found', num=cnum))
        elif action == 'delete':
            attachments = Attachment.select(self.env, ticket.realm, ticket.id)
            data.update(attachments=list(attachments))

        add_stylesheet(req, 'common/css/ticket.css')
        return 'ticket_delete.html', data, None

    def get_comment_history(self, cnum=None, cdate=None):
        """Retrieve the edit history of a comment identified by its number or
        date.
        """
        if cdate is None:
            row = self._find_change(cnum)
            if not row:
                return
            ts0, author0, last_comment = row
        else:
            ts0, author0, last_comment = to_utimestamp(cdate), None, None
        with self.env.db_query as db:
            # Get last comment and author if not available
            if last_comment is None:
                last_comment = ''
                for author0, last_comment in db("""
                        SELECT author, newvalue FROM ticket_change
                        WHERE ticket=%s AND time=%s AND field='comment'
                        """, (self.id, ts0)):
                    break
            if author0 is None:
                for author0, last_comment in db("""
                        SELECT author, newvalue FROM ticket_change
                        WHERE ticket=%%s AND time=%%s AND NOT field %s LIMIT 1
                        """ % db.prefix_match(),
                        (self.id, ts0, db.prefix_match_value('_'))):
                    break
                else:
                    return

            # Get all fields of the form "_comment%d"
            rows = db("""SELECT field, author, oldvalue, newvalue
                         FROM ticket_change
                         WHERE ticket=%%s AND time=%%s AND field %s
                         """ % db.prefix_match(),
                         (self.id, ts0, db.prefix_match_value('_comment')))
            rows = sorted((int(field[8:]), author, old, new)
                          for field, author, old, new in rows)
            history = []
            for rev, author, comment, ts in rows:
                history.append((rev, from_utimestamp(long(ts0)), author0,
                                comment))
                ts0, author0 = ts, author
            history.sort()
            rev = history[-1][0] + 1 if history else 0
            history.append((rev, from_utimestamp(long(ts0)), author0,
                            last_comment))
            return history

    def get_change(self, cnum=None, cdate=None, db=None):
        """Return a ticket change by its number or date.

        :since 1.0: the `db` parameter is no longer needed and will be removed
        in version 1.1.1
        """
        if cdate is None:
            row = self._find_change(cnum)
            if not row:
                return
            cdate = from_utimestamp(row[0])
        ts = to_utimestamp(cdate)
        fields = {}
        change = {"date": cdate, "fields": fields}
        for field, author, old, new in self.env.db_query(
            """
                SELECT field, author, oldvalue, newvalue
                FROM ticket_change WHERE ticket=%s AND time=%s
                """,
            (self.id, ts),
        ):
            fields[field] = {"author": author, "old": old, "new": new}
            if field == "comment":
                change["author"] = author
            elif not field.startswith("_"):
                change.setdefault("author", author)
        if fields:
            return change

    def get_search_results(self, req, terms, filters):
        if not 'wiki' in filters:
            return
        db = self.env.get_db_cnx()
        sql_query, args = search_to_sql(db, ['w1.name', 'w1.author', 'w1.text'],
                                        terms)
        cursor = db.cursor()
        cursor.execute("SELECT w1.name,w1.time,w1.author,w1.text "
                       "FROM wiki w1,"
                       "(SELECT name,max(version) AS ver "
                       "FROM wiki GROUP BY name) w2 "
                       "WHERE w1.version = w2.ver AND w1.name = w2.name "
                       "AND " + sql_query, args)

        wiki_realm = Resource('wiki')
        for name, ts, author, text in cursor:
            page = wiki_realm(id=name)
            if 'WIKI_VIEW' in req.perm(page):
                yield (get_resource_url(self.env, page, req.href),
                       '%s: %s' % (name, shorten_line(text)),
                       from_utimestamp(ts), author,
                       shorten_result(text, terms))
        
        # Attachments
        for result in AttachmentModule(self.env).get_search_results(
            req, wiki_realm, terms):
            yield result

    def get_change(self, cnum=None, cdate=None, db=None):
        """Return a ticket change by its number or date.

        :since 1.0: the `db` parameter is no longer needed and will be removed
        in version 1.1.1
        """
        if cdate is None:
            row = self._find_change(cnum)
            if not row:
                return
            cdate = from_utimestamp(row[0])
        ts = to_utimestamp(cdate)
        fields = {}
        change = {'date': cdate, 'fields': fields}
        for field, author, old, new in self.env.db_query("""
                SELECT field, author, oldvalue, newvalue
                FROM ticket_change WHERE ticket=%s AND time=%s
                """, (self.id, ts)):
            fields[field] = {'author': author, 'old': old, 'new': new}
            if field == 'comment':
                change['author'] = author
            elif not field.startswith('_'):
                change.setdefault('author', author)
        if fields:
            return change

    def get_history(self, item, db=None):
        if not item in self.pool.get_items():
            raise Exception("Item not in pool")

        if item.is_new():
            return

        if db is None:
            db = self.env.get_db_cnx()

        cursor = db.cursor()
        query = """
                SELECT v.id, v.time, v.author, v.ipnr, v.comment
                FROM asa_version v"""
        if isinstance(item, Entity):  # it's a spec
            query += """
                    INNER JOIN asa_spec s ON s.version_id=v.id
                    WHERE s.name='%s'
                    """ % (
                item.get_name()
            )
        else:  # it's an artifact
            query += """
                    INNER JOIN asa_artifact a ON a.version_id=v.id
                    WHERE a.id=%d""" % (
                item.get_id()
            )

        if not self.version is None:
            query += " AND v.id <= %s" % (self.version,)
        query += " ORDER BY v.id DESC"
        cursor.execute(query)
        for version, ts, author, ipnr, comment in cursor:
            yield version, from_utimestamp(ts), author, ipnr, comment

    def milestones(self):
        """Dictionary containing milestone data, indexed by name.

        Milestone data consist of a tuple containing the name, the
        datetime objects for due and completed dates and the
        description.
        """
        milestones = {}
        for name, due, completed, description in self.env.db_query("""
                SELECT name, due, completed, description FROM milestone
                """):
            milestones[name] = (name,
                    from_utimestamp(due) if due else None,
                    from_utimestamp(completed) if completed else None,
                    description or '')
        return milestones

    def _fetch_message(self, name):
        """
        Retrieves data representing an individual project message from the 
        database, and unpacks this into a values dictionary.

        If the name provided does not match a row in the project_message 
        table, we raise a ResourceNotFound exception."""

        db = self.env.get_read_db()
        cursor = db.cursor()
        cursor.execute("""SELECT name, message, button, mode, groups,
                                 start, "end", author, created_at
                          FROM project_message
                          WHERE name=%s""", (name,))
        row = cursor.fetchone()
        if row:
            for field, value in izip(self.message_keys, row):
                if field == 'groups':
                    self.values[field] = json.loads(value)
                if field in ['start', 'date']:
                    self.values[field] = from_utimestamp(value)
                else:
                    self.values[field] = value
        else:
            raise ResourceNotFound("Project message '%s' does not exist.", (name))

    def _fetch_ticket(self, tkt_id):
        row = None
        if self.id_is_valid(tkt_id):
            # Fetch the standard ticket fields
            for row in self.env.db_query("SELECT %s FROM ticket WHERE id=%%s" %
                                         ','.join(self.std_fields), (tkt_id,)):
                break
        if not row:
            raise ResourceNotFound(_("Ticket %(id)s does not exist.",
                                     id=tkt_id), _("Invalid ticket number"))

        self.id = tkt_id
        for i, field in enumerate(self.std_fields):
            value = row[i]
            if field in self.time_fields:
                self.values[field] = from_utimestamp(value)
            elif value is None:
                self.values[field] = empty
            else:
                self.values[field] = value

        # Fetch custom fields if available
        for name, value in self.env.db_query("""
                SELECT name, value FROM ticket_custom WHERE ticket=%s
                """, (tkt_id,)):
            if name in self.custom_fields:
                if name in self.time_fields:
                    self.values[name] = _db_str_to_datetime(value)
                elif value is None:
                    self.values[name] = empty
                else:
                    self.values[name] = value

 def _fetch(self, name, version=None, db=None):
     if not db:
         db = self.env.get_db_cnx()
     cursor = db.cursor()
     if version is not None:
         cursor.execute("SELECT version,time,author,text,comment,readonly "
                        "FROM wiki "
                        "WHERE name=%s AND version=%s",
                        (name, int(version)))
     else:
         cursor.execute("SELECT version,time,author,text,comment,readonly "
                        "FROM wiki "
                        "WHERE name=%s ORDER BY version DESC LIMIT 1",
                        (name,))
     row = cursor.fetchone()
     if row:
         version, time, author, text, comment, readonly = row
         self.version = int(version)
         self.author = author
         self.time = from_utimestamp(time)
         self.text = text
         self.comment = comment
         self.readonly = readonly and int(readonly) or 0
     else:
         self.version = 0
         self.text = self.comment = self.author = ''
         self.time = None
         self.readonly = 0

 def get_timeline_events(self, req, start, stop, filters):
     if 'project changes' in filters:
         cnx=self.env.get_db_cnx()
         cur=cnx.cursor()
         cur.execute("select who,change,time from project_change where time>=%s AND time<=%s"%\
                      (to_utimestamp(start), to_utimestamp(stop)));
         for who,change,ts in cur:
             yield('project',from_utimestamp(ts),who,change)