body = text

        #changeset

        if revision:

            if line_no:

                line = _('on line %s') % line_no

            subj = safe_unicode(

                h.link_to('Re commit: %(desc)s %(line)s' % \

                          {'desc': desc, 'line': line},

                          h.url('changeset_home', repo_name=repo.repo_name,

                                revision=revision,

                                anchor='comment-%s' % comment.comment_id,

                                qualified=True,

                          )

                )

            )

            notification_type = Notification.TYPE_CHANGESET_COMMENT

            # get the current participants of this changeset

            recipients = ChangesetComment.get_users(revision=revision)

            # add changeset author if it's in rhodecode system

            recipients += [User.get_by_email(author_email)]

        #pull request

        elif pull_request:

            subj = safe_unicode(

                h.link_to('Re pull request: %(desc)s %(line)s' % \

                          {'desc': desc, 'line': line},

                          h.url('pullrequest_show',

                                repo_name=pull_request.other_repo.repo_name,

                                pull_request_id=pull_request.pull_request_id,

                                anchor='comment-%s' % comment.comment_id,

                                qualified=True,

                          )

                )

            )

            notification_type = Notification.TYPE_PULL_REQUEST_COMMENT

            # get the current participants of this pull request

            recipients = ChangesetComment.get_users(pull_request_id=

                                                pull_request.pull_request_id)

            # add pull request author

            recipients += [pull_request.author]

        # create notification objects, and emails

        NotificationModel().create(

          created_by=user, subject=subj, body=body,

          recipients=recipients, type_=notification_type,

          email_kwargs={'status_change': status_change}

        )

        mention_recipients = set(self._extract_mentions(body))\

                                .difference(recipients)

        if mention_recipients:

            subj = _('[Mention]') + ' ' + subj

            NotificationModel().create(

                created_by=user, subject=subj, body=body,

                recipients=mention_recipients,

                type_=notification_type,

                email_kwargs={'status_change': status_change}

            )

        return comment

    def delete(self, comment):

        """

        Deletes given comment

        :param comment_id:

        """

        comment = self.__get_changeset_comment(comment)

        self.sa.delete(comment)

        return comment

    def get_comments(self, repo_id, revision=None, pull_request=None):

        """

        Get's main comments based on revision or pull_request_id

        :param repo_id:

        :type repo_id:

        :param revision:

        :type revision:

        :param pull_request:

        :type pull_request:

        """

        q = ChangesetComment.query()\

                .filter(ChangesetComment.repo_id == repo_id)\

                .filter(ChangesetComment.line_no == None)\

                .filter(ChangesetComment.f_path == None)

        if revision:

            q = q.filter(ChangesetComment.revision == revision)

        elif pull_request:

            pull_request = self.__get_pull_request(pull_request)

            q = q.filter(ChangesetComment.pull_request == pull_request)

        else:

            raise Exception('Please specify revision or pull_request')

        return q.all()

    def get_inline_comments(self, repo_id, revision=None, pull_request=None):

        q = self.sa.query(ChangesetComment)\

            .filter(ChangesetComment.repo_id == repo_id)\

            .filter(ChangesetComment.line_no != None)\

            .filter(ChangesetComment.f_path != None)\

            .order_by(ChangesetComment.comment_id.asc())\

        if revision:

            q = q.filter(ChangesetComment.revision == revision)

        elif pull_request:

            pull_request = self.__get_pull_request(pull_request)

            q = q.filter(ChangesetComment.pull_request == pull_request)

        else:

            raise Exception('Please specify revision or pull_request_id')

        comments = q.all()

        paths = defaultdict(lambda: defaultdict(list))

        for co in comments:

            paths[co.f_path][co.line_no].append(co)

        return paths.items()

        state is not valid and needs to be invalidated

        :param key:

        """

        key, _prefix, _org_key = cls._get_key(key)

        inv = cls._get_or_create_key(key, _prefix, _org_key)

        if inv and inv.cache_active is False:

            return inv

    @classmethod

    def set_invalidate(cls, key):

        """

        Mark this Cache key for invalidation

        :param key:

        """

        key, _prefix, _org_key = cls._get_key(key)

        inv_objs = Session().query(cls).filter(cls.cache_args == _org_key).all()

        log.debug('marking %s key[s] %s for invalidation' % (len(inv_objs),

                                                             _org_key))

        try:

            for inv_obj in inv_objs:

                if inv_obj:

                    inv_obj.cache_active = False

                Session().add(inv_obj)

            Session().commit()

        except Exception:

            log.error(traceback.format_exc())

            Session().rollback()

    @classmethod

    def set_valid(cls, key):

        """

        Mark this cache key as active and currently cached

        :param key:

        """

        inv_obj = cls.get_by_key(key)

        inv_obj.cache_active = True

        Session().add(inv_obj)

        Session().commit()

    @classmethod

    def get_cache_map(cls):

        class cachemapdict(dict):

            def __init__(self, *args, **kwargs):

                fixkey = kwargs.get('fixkey')

                if fixkey:

                    del kwargs['fixkey']

                self.fixkey = fixkey

                super(cachemapdict, self).__init__(*args, **kwargs)

            def __getattr__(self, name):

                key = name

                if self.fixkey:

                    key, _prefix, _org_key = cls._get_key(key)

                if key in self.__dict__:

                    return self.__dict__[key]

                else:

                    return self[key]

            def __getitem__(self, key):

                if self.fixkey:

                    key, _prefix, _org_key = cls._get_key(key)

                try:

                    return super(cachemapdict, self).__getitem__(key)

                except KeyError:

                    return

        cache_map = cachemapdict(fixkey=True)

        for obj in cls.query().all():

            cache_map[obj.cache_key] = cachemapdict(obj.get_dict())

        return cache_map

class ChangesetComment(Base, BaseModel):

    __tablename__ = 'changeset_comments'

    __table_args__ = (

        Index('cc_revision_idx', 'revision'),

        {'extend_existing': True, 'mysql_engine': 'InnoDB',

         'mysql_charset': 'utf8'},

    )

    comment_id = Column('comment_id', Integer(), nullable=False, primary_key=True)

    repo_id = Column('repo_id', Integer(), ForeignKey('repositories.repo_id'), nullable=False)

    revision = Column('revision', String(40), nullable=True)

    pull_request_id = Column("pull_request_id", Integer(), ForeignKey('pull_requests.pull_request_id'), nullable=True)

    line_no = Column('line_no', Unicode(10), nullable=True)

    f_path = Column('f_path', Unicode(1000), nullable=True)

    user_id = Column('user_id', Integer(), ForeignKey('users.user_id'), nullable=False)

    text = Column('text', Unicode(25000), nullable=False)

    author = relationship('User', lazy='joined')

    repo = relationship('Repository')

    status_change = relationship('ChangesetStatus', uselist=False)

    pull_request = relationship('PullRequest', lazy='joined')

    @classmethod

    def get_users(cls, revision=None, pull_request_id=None):

        """

        Returns user associated with this ChangesetComment. ie those

        who actually commented

        :param cls:

        :param revision:

        """

        q = Session().query(User)\

                .join(ChangesetComment.author)

        if revision:

            q = q.filter(cls.revision == revision)

        elif pull_request_id:

            q = q.filter(cls.pull_request_id == pull_request_id)

        return q.all()

class ChangesetStatus(Base, BaseModel):

    __tablename__ = 'changeset_statuses'

    __table_args__ = (

        Index('cs_revision_idx', 'revision'),

        Index('cs_version_idx', 'version'),

        UniqueConstraint('repo_id', 'revision', 'version'),

        {'extend_existing': True, 'mysql_engine': 'InnoDB',

         'mysql_charset': 'utf8'}

    )

    STATUS_NOT_REVIEWED = DEFAULT = 'not_reviewed'

    STATUS_APPROVED = 'approved'

    STATUS_REJECTED = 'rejected'

    STATUS_UNDER_REVIEW = 'under_review'

    STATUSES = [

        (STATUS_NOT_REVIEWED, _("Not Reviewed")),  # (no icon) and default

        (STATUS_APPROVED, _("Approved")),

        (STATUS_REJECTED, _("Rejected")),

        (STATUS_UNDER_REVIEW, _("Under Review")),

    ]

    changeset_status_id = Column('changeset_status_id', Integer(), nullable=False, primary_key=True)

    repo_id = Column('repo_id', Integer(), ForeignKey('repositories.repo_id'), nullable=False)

    user_id = Column("user_id", Integer(), ForeignKey('users.user_id'), nullable=False, unique=None)

    revision = Column('revision', String(40), nullable=False)

    status = Column('status', String(128), nullable=False, default=DEFAULT)

    changeset_comment_id = Column('changeset_comment_id', Integer(), ForeignKey('changeset_comments.comment_id'))

    modified_at = Column('modified_at', DateTime(), nullable=False, default=datetime.datetime.now)

    version = Column('version', Integer(), nullable=False, default=0)

    pull_request_id = Column("pull_request_id", Integer(), ForeignKey('pull_requests.pull_request_id'), nullable=True)

    author = relationship('User', lazy='joined')

    repo = relationship('Repository')

    comment = relationship('ChangesetComment', lazy='joined')

    pull_request = relationship('PullRequest', lazy='joined')

    def __unicode__(self):

        return u"<%s('%s:%s')>" % (

            self.__class__.__name__,

            self.status, self.author

        )

    @classmethod

    def get_status_lbl(cls, value):

        return dict(cls.STATUSES).get(value)

    @property

    def status_lbl(self):

        return ChangesetStatus.get_status_lbl(self.status)

class PullRequest(Base, BaseModel):

    __tablename__ = 'pull_requests'

    __table_args__ = (

        {'extend_existing': True, 'mysql_engine': 'InnoDB',

         'mysql_charset': 'utf8'},

    )

    STATUS_NEW = u'new'

    STATUS_OPEN = u'open'

    STATUS_CLOSED = u'closed'

    pull_request_id = Column('pull_request_id', Integer(), nullable=False, primary_key=True)

    title = Column('title', Unicode(256), nullable=True)

    description = Column('description', UnicodeText(10240), nullable=True)

    status = Column('status', Unicode(256), nullable=False, default=STATUS_NEW)

    created_on = Column('created_on', DateTime(timezone=False), nullable=False, default=datetime.datetime.now)

    updated_on = Column('updated_on', DateTime(timezone=False), nullable=False, default=datetime.datetime.now)

    user_id = Column("user_id", Integer(), ForeignKey('users.user_id'), nullable=False, unique=None)

    _revisions = Column('revisions', UnicodeText(20500))  # 500 revisions max

    org_repo_id = Column('org_repo_id', Integer(), ForeignKey('repositories.repo_id'), nullable=False)

    org_ref = Column('org_ref', Unicode(256), nullable=False)