other_repos_info = {}

        def add_other_repo(repo, branch_rev=None):

            if repo.repo_name in other_repos_info: # shouldn't happen

                return

            c.other_repos.append((repo.repo_name, repo.repo_name))

            other_refs, selected_other_ref = self._get_repo_refs(repo.scm_instance, branch_rev=branch_rev)

            other_repos_info[repo.repo_name] = {

                'user': dict(user_id=repo.user.user_id,

                             username=repo.user.username,

                             firstname=repo.user.firstname,

                             lastname=repo.user.lastname,

                             gravatar_link=h.gravatar_url(repo.user.email, 14)),

                'description': repo.description.split('\n', 1)[0],

                'revs': h.select('other_ref', selected_other_ref, other_refs, class_='refs')

            }

        # add org repo to other so we can open pull request against peer branches on itself

        add_other_repo(org_repo, branch_rev=org_rev)

        c.default_other_repo = org_repo.repo_name

        # gather forks and add to this list ... even though it is rare to

        # request forks to pull from their parent

        for fork in org_repo.forks:

            add_other_repo(fork)

        # add parents of this fork also, but only if it's not empty

        if org_repo.parent and org_repo.parent.scm_instance.revisions:

            add_other_repo(org_repo.parent)

            c.default_other_repo = org_repo.parent.repo_name

        c.default_other_repo_info = other_repos_info[c.default_other_repo]

        c.other_repos_info = json.dumps(other_repos_info)

        return render('/pullrequests/pullrequest.html')

    @LoginRequired()

    @NotAnonymous()

    @HasRepoPermissionAnyDecorator('repository.read', 'repository.write',

                                   'repository.admin')

    def create(self, repo_name):

        repo = RepoModel()._get_repo(repo_name)

        try:

            _form = PullRequestForm(repo.repo_id)().to_python(request.POST)

        except formencode.Invalid, errors:

            log.error(traceback.format_exc())

            log.error(str(errors))

            msg = _('Error creating pull request: %s') % errors.msg

            h.flash(msg, 'error')

            raise HTTPBadRequest

        # heads up: org and other might seem backward here ...

        org_repo_name = _form['org_repo']

        org_ref = _form['org_ref'] # will have merge_rev as rev but symbolic name

        org_repo = RepoModel()._get_repo(org_repo_name)

        (org_ref_type,

         org_ref_name,

         org_rev) = org_ref.split(':')

        other_repo_name = _form['other_repo']

        other_ref = _form['other_ref'] # will have symbolic name and head revision

        other_repo = RepoModel()._get_repo(other_repo_name)

        (other_ref_type,

         other_ref_name,

         other_rev) = other_ref.split(':')

        cs_ranges, _cs_ranges_not, ancestor_rev = \

            CompareController._get_changesets(org_repo.scm_instance.alias,

                                              other_repo.scm_instance, other_rev, # org and other "swapped"

                                              org_repo.scm_instance, org_rev,

                                              )

        revisions = [cs.raw_id for cs in cs_ranges]

        # hack: ancestor_rev is not an other_rev but we want to show the

        # requested destination and have the exact ancestor

        other_ref = '%s:%s:%s' % (other_ref_type, other_ref_name, ancestor_rev)

        reviewers = _form['review_members']

        title = _form['pullrequest_title']

        if not title:

            title = '%s#%s to %s#%s' % (org_repo_name, h.short_ref(org_ref_type, org_ref_name),

                                        other_repo_name, h.short_ref(other_ref_type, other_ref_name))

        description = _form['pullrequest_desc'].strip() or _('No description')

        try:

            pull_request = PullRequestModel().create(

                self.authuser.user_id, org_repo_name, org_ref, other_repo_name,

                other_ref, revisions, reviewers, title, description

            )

            Session().commit()

            h.flash(_('Successfully opened new pull request'),

                    category='success')

        except Exception:

            h.flash(_('Error occurred while creating pull request'),

                    category='error')

            log.error(traceback.format_exc())

            return redirect(url('pullrequest_home', repo_name=repo_name))

        return redirect(pull_request.url())

    @LoginRequired()

    @NotAnonymous()

    @HasRepoPermissionAnyDecorator('repository.read', 'repository.write',

                                   'repository.admin')

    def copy_update(self, repo_name, pull_request_id):

        old_pull_request = PullRequest.get_or_404(pull_request_id)

        assert old_pull_request.other_repo.repo_name == repo_name

        if old_pull_request.is_closed():

            raise HTTPForbidden()

        org_repo = RepoModel()._get_repo(old_pull_request.org_repo.repo_name)

        org_ref_type, org_ref_name, org_rev = old_pull_request.org_ref.split(':')

        updaterev = request.POST.get('updaterev')

        if updaterev:

            new_org_rev = self._get_ref_rev(org_repo, 'rev', updaterev)

        else:

            # assert org_ref_type == 'branch', org_ref_type # TODO: what if not?

            new_org_rev = self._get_ref_rev(org_repo, org_ref_type, org_ref_name)

        other_repo = RepoModel()._get_repo(old_pull_request.other_repo.repo_name)

        other_ref_type, other_ref_name, other_rev = old_pull_request.other_ref.split(':') # other_rev is ancestor

        #assert other_ref_type == 'branch', other_ref_type # TODO: what if not?

        new_other_rev = self._get_ref_rev(other_repo, other_ref_type, other_ref_name)

        cs_ranges, _cs_ranges_not, ancestor_rev = CompareController._get_changesets(org_repo.scm_instance.alias,

            other_repo.scm_instance, new_other_rev, # org and other "swapped"

            org_repo.scm_instance, new_org_rev)

        old_revisions = set(old_pull_request.revisions)

        revisions = [cs.raw_id for cs in cs_ranges]

        new_revisions = [r for r in revisions if r not in old_revisions]

        lost = old_revisions.difference(revisions)

        infos = ['','', 'This is an update of %s "%s".' %

                 (h.canonical_url('pullrequest_show', repo_name=old_pull_request.other_repo.repo_name,

                      pull_request_id=pull_request_id),

                  old_pull_request.title)]

        if lost:

            infos.append(_('Missing changesets since the previous version:'))

            for r in old_pull_request.revisions:

                if r in lost:

                    desc = org_repo.get_changeset(r).message.split('\n')[0]

                    infos.append('  %s "%s"' % (h.short_id(r), desc))

        if new_revisions:

            infos.append(_('New changesets on %s %s since the previous version:') % (org_ref_type, org_ref_name))

            for r in reversed(revisions):

                if r in new_revisions:

                    desc = org_repo.get_changeset(r).message.split('\n')[0]

                    infos.append('  %s %s' % (h.short_id(r), h.shorter(desc, 80)))

            if ancestor_rev == other_rev:

                infos.append(_("Ancestor didn't change - show diff since previous version:"))

                infos.append(h.canonical_url('compare_url',

                                 repo_name=org_repo.repo_name, # other_repo is always same as repo_name

                                 org_ref_type='rev', org_ref_name=h.short_id(org_rev), # use old org_rev as base

                                 other_ref_type='rev', other_ref_name=h.short_id(new_org_rev),

                                 )) # note: linear diff, merge or not doesn't matter

            else:

                infos.append(_('This pull request is based on another %s revision and there is no simple diff.') % other_ref_name)

        else:

           infos.append(_('No changes found on %s %s since previous version.') % (org_ref_type, org_ref_name))

           # TODO: fail?

        # hack: ancestor_rev is not an other_ref but we want to show the

        # requested destination and have the exact ancestor

        new_other_ref = '%s:%s:%s' % (other_ref_type, other_ref_name, ancestor_rev)

        new_org_ref = '%s:%s:%s' % (org_ref_type, org_ref_name, new_org_rev)

        reviewers = [r.user_id for r in old_pull_request.reviewers]

        try:

            old_title, old_v = re.match(r'(.*)\(v(\d+)\)\s*$', old_pull_request.title).groups()

            v = int(old_v) + 1

        except (AttributeError, ValueError):

            old_title = old_pull_request.title

            v = 2

        title = '%s (v%s)' % (old_title.strip(), v)

        description = (old_pull_request.description.rstrip() +

                       '\n'.join(infos))

        try:

            pull_request = PullRequestModel().create(

                self.authuser.user_id,

                old_pull_request.org_repo.repo_name, new_org_ref,

                old_pull_request.other_repo.repo_name, new_other_ref,

                revisions, reviewers, title, description

            )

        except Exception:

            h.flash(_('Error occurred while creating pull request'),

                    category='error')

            log.error(traceback.format_exc())

        ChangesetCommentsModel().create(

            text=_('Closed, replaced by %s .') % h.canonical_url('pullrequest_show',

                                                   repo_name=old_pull_request.other_repo.repo_name,

                                                   pull_request_id=pull_request.pull_request_id),

            repo=old_pull_request.other_repo.repo_id,

            user=c.authuser.user_id,

            pull_request=pull_request_id,

            closing_pr=True)

        PullRequestModel().close_pull_request(pull_request_id)

        Session().commit()

        h.flash(_('Pull request update created'),

                category='success')

    # pullrequest_post for PR editing

    @LoginRequired()

    @NotAnonymous()

    @HasRepoPermissionAnyDecorator('repository.read', 'repository.write',

                                   'repository.admin')

    def post(self, repo_name, pull_request_id):

        repo = RepoModel()._get_repo(repo_name)

        pull_request = PullRequest.get_or_404(pull_request_id)

        old_description = pull_request.description

        _form = PullRequestPostForm()().to_python(request.POST)

        pull_request.title = _form['pullrequest_title']

        pull_request.description = _form['pullrequest_desc'].strip() or _('No description')

        PullRequestModel().mention_from_description(pull_request, old_description)

        Session().commit()

        h.flash(_('Pull request updated'), category='success')

    # pullrequest_update for updating reviewer list

    @LoginRequired()

    @NotAnonymous()

    @HasRepoPermissionAnyDecorator('repository.read', 'repository.write',

                                   'repository.admin')

    @jsonify

    def update(self, repo_name, pull_request_id):

        pull_request = PullRequest.get_or_404(pull_request_id)

        if pull_request.is_closed():

            raise HTTPForbidden()

        #only owner or admin can update it

        owner = pull_request.author.user_id == c.authuser.user_id

        repo_admin = h.HasRepoPermissionAny('repository.admin')(c.repo_name)

        if h.HasPermissionAny('hg.admin') or repo_admin or owner:

            reviewers_ids = map(int, filter(lambda v: v not in [None, ''],

                request.POST.get('reviewers_ids', '').split(',')))

            PullRequestModel().update_reviewers(pull_request_id, reviewers_ids)

            Session().commit()

            return True

        raise HTTPForbidden()

    @LoginRequired()

    @NotAnonymous()

    @HasRepoPermissionAnyDecorator('repository.read', 'repository.write',

                                   'repository.admin')

    @jsonify

    def delete(self, repo_name, pull_request_id):

        pull_request = PullRequest.get_or_404(pull_request_id)

        #only owner can delete it !

        if pull_request.author.user_id == c.authuser.user_id:

            PullRequestModel().delete(pull_request)

            Session().commit()

            h.flash(_('Successfully deleted pull request'),

                    category='success')

            return redirect(url('my_account_pullrequests'))

        raise HTTPForbidden()

    @LoginRequired()

    @HasRepoPermissionAnyDecorator('repository.read', 'repository.write',

                                   'repository.admin')

    def show(self, repo_name, pull_request_id, extra=None):

        repo_model = RepoModel()

        c.users_array = repo_model.get_users_js()

        c.user_groups_array = repo_model.get_user_groups_js()

        c.pull_request = PullRequest.get_or_404(pull_request_id)

        c.allowed_to_change_status = self._get_is_allowed_change_status(c.pull_request)

        cc_model = ChangesetCommentsModel()

        cs_model = ChangesetStatusModel()

        # pull_requests repo_name we opened it against

        # ie. other_repo must match

        if repo_name != c.pull_request.other_repo.repo_name:

            raise HTTPNotFound

        # load compare data into template context

        self._load_compare_data(c.pull_request, enable_comments=True)

        # inline comments

        c.inline_cnt = 0

        c.inline_comments = cc_model.get_inline_comments(

                                c.db_repo.repo_id,

                                pull_request=pull_request_id)

        # count inline comments

        for __, lines in c.inline_comments:

            for comments in lines.values():

                c.inline_cnt += len(comments)

        # comments

        c.comments = cc_model.get_comments(c.db_repo.repo_id,

                                           pull_request=pull_request_id)

        # (badly named) pull-request status calculation based on reviewer votes

        (c.pull_request_reviewers,

         c.pull_request_pending_reviewers,

         c.current_voting_result,

         ) = cs_model.calculate_pull_request_result(c.pull_request)

        c.changeset_statuses = ChangesetStatus.STATUSES

        c.as_form = False

        c.ancestor = None # there is one - but right here we don't know which

        return render('/pullrequests/pullrequest_show.html')

    @LoginRequired()

    @NotAnonymous()

    @HasRepoPermissionAnyDecorator('repository.read', 'repository.write',

                                   'repository.admin')

    @jsonify

    def comment(self, repo_name, pull_request_id):

        pull_request = PullRequest.get_or_404(pull_request_id)

        status = 0

        close_pr = False

        allowed_to_change_status = self._get_is_allowed_change_status(pull_request)

        if allowed_to_change_status:

            status = request.POST.get('changeset_status')

            close_pr = request.POST.get('save_close')

        text = request.POST.get('text', '').strip() or _('No comments.')

        if close_pr:

            text = _('Closing.') + '\n' + text

        comm = ChangesetCommentsModel().create(

            text=text,

            repo=c.db_repo.repo_id,

            user=c.authuser.user_id,

            pull_request=pull_request_id,

            f_path=request.POST.get('f_path'),

            line_no=request.POST.get('line'),

            status_change=(ChangesetStatus.get_status_lbl(status)

                           if status and allowed_to_change_status else None),

            closing_pr=close_pr

        )

        action_logger(self.authuser,

                      'user_commented_pull_request:%s' % pull_request_id,

                      c.db_repo, self.ip_addr, self.sa)

        if allowed_to_change_status:

            # get status if set !

            if status:

                ChangesetStatusModel().set_status(

                    c.db_repo.repo_id,

                    status,

                    c.authuser.user_id,

                    comm,

                    pull_request=pull_request_id

                )

            if close_pr:

                PullRequestModel().close_pull_request(pull_request_id)

                action_logger(self.authuser,

                              'user_closed_pull_request:%s' % pull_request_id,

                              c.db_repo, self.ip_addr, self.sa)

        Session().commit()

        if not request.environ.get('HTTP_X_PARTIAL_XHR'):

        data = {

           'target_id': h.safeid(h.safe_unicode(request.POST.get('f_path'))),

        }

        if comm:

            c.co = comm

            data.update(comm.get_dict())

            data.update({'rendered_text':

                         render('changeset/changeset_comment_block.html')})

        return data

    @LoginRequired()

    @NotAnonymous()

    @HasRepoPermissionAnyDecorator('repository.read', 'repository.write',

                                   'repository.admin')

    @jsonify

    def delete_comment(self, repo_name, comment_id):

        co = ChangesetComment.get(comment_id)

        if co.pull_request.is_closed():

            #don't allow deleting comments on closed pull request

            raise HTTPForbidden()

        owner = co.author.user_id == c.authuser.user_id

        repo_admin = h.HasRepoPermissionAny('repository.admin')(c.repo_name)

        if h.HasPermissionAny('hg.admin') or repo_admin or owner:

            ChangesetCommentsModel().delete(comment=co)

            Session().commit()

            return True

        else:

            raise HTTPForbidden()

        Return True if the existing cache registration still was valid.

        Return False to indicate that it had been invalidated and caches should be refreshed.

        """

        key = (repo_name + '_' + kind) if kind else repo_name

        cache_key = cls._get_cache_key(key)

        if valid_cache_keys and cache_key in valid_cache_keys:

            return True

        try:

            inv_obj = cls.query().filter(cls.cache_key == cache_key).scalar()

            if not inv_obj:

                inv_obj = CacheInvalidation(cache_key, repo_name)

            was_valid = inv_obj.cache_active

            inv_obj.cache_active = True

            Session().add(inv_obj)

            Session().commit()

            return was_valid

        except Exception:

            log.error(traceback.format_exc())

            Session().rollback()

            return False

    @classmethod

    def get_valid_cache_keys(cls):

        """

        Return opaque object with information of which caches still are valid

        and can be used without checking for invalidation.

        """

        return set(inv_obj.cache_key for inv_obj in cls.query().filter(cls.cache_active).all())

class ChangesetComment(Base, BaseModel):

    __tablename__ = 'changeset_comments'

    __table_args__ = (

        Index('cc_revision_idx', 'revision'),

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

         'mysql_charset': 'utf8', 'sqlite_autoincrement': True},

    )

    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)

    hl_lines = Column('hl_lines', Unicode(512), 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', UnicodeText(25000), nullable=False)

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

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

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

    repo = relationship('Repository')

    status_change = relationship('ChangesetStatus', cascade="all, delete, delete-orphan")

    pull_request = relationship('PullRequest')

    @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', 'sqlite_autoincrement': True}

    )

    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')

    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', 'sqlite_autoincrement': True},

    )

    # values for .status

    STATUS_NEW = u'new'

    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) # only for closedness, not approve/reject/etc

    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)

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

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

    @hybrid_property

    def revisions(self):

        return self._revisions.split(':')

    @revisions.setter

    def revisions(self, val):

        self._revisions = ':'.join(val)

    @property

    def org_ref_parts(self):

        return self.org_ref.split(':')

    @property

    def other_ref_parts(self):

        return self.other_ref.split(':')

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

    reviewers = relationship('PullRequestReviewers',

                             cascade="all, delete, delete-orphan")

    org_repo = relationship('Repository', primaryjoin='PullRequest.org_repo_id==Repository.repo_id')

    other_repo = relationship('Repository', primaryjoin='PullRequest.other_repo_id==Repository.repo_id')

    statuses = relationship('ChangesetStatus')

    comments = relationship('ChangesetComment',

                             cascade="all, delete, delete-orphan")

    def is_closed(self):

        return self.status == self.STATUS_CLOSED

    @property

    def last_review_status(self):

        return str(self.statuses[-1].status) if self.statuses else ''

    def __json__(self):

        return dict(

            revisions=self.revisions

        )

    def url(self, **kwargs):

        canonical = kwargs.pop('canonical', None)

        import kallithea.lib.helpers as h

        s = '/' + self.title

        b = self.org_ref_parts[1]

            s = '/_%s_%s' % (b, s)

        kwargs['extra'] = urlreadable(s)

        if canonical:

            return h.canonical_url('pullrequest_show', repo_name=self.other_repo.repo_name,

                                   pull_request_id=self.pull_request_id, **kwargs)

        return h.url('pullrequest_show', repo_name=self.other_repo.repo_name,

                     pull_request_id=self.pull_request_id, **kwargs)

class PullRequestReviewers(Base, BaseModel):

    __tablename__ = 'pull_request_reviewers'

    __table_args__ = (

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

         'mysql_charset': 'utf8', 'sqlite_autoincrement': True},

    )

    def __init__(self, user=None, pull_request=None):

        self.user = user

        self.pull_request = pull_request

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

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

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

    user = relationship('User')

    pull_request = relationship('PullRequest')

class Notification(Base, BaseModel):

    __tablename__ = 'notifications'

    __table_args__ = (

        Index('notification_type_idx', 'type'),

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

         'mysql_charset': 'utf8', 'sqlite_autoincrement': True},

    )

    TYPE_CHANGESET_COMMENT = u'cs_comment'

    TYPE_MESSAGE = u'message'

    TYPE_MENTION = u'mention'

    TYPE_REGISTRATION = u'registration'

    TYPE_PULL_REQUEST = u'pull_request'

    TYPE_PULL_REQUEST_COMMENT = u'pull_request_comment'

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

    subject = Column('subject', Unicode(512), nullable=True)

    body = Column('body', UnicodeText(50000), nullable=True)

    created_by = Column("created_by", Integer(), ForeignKey('users.user_id'), nullable=True)

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

    type_ = Column('type', Unicode(256))

    created_by_user = relationship('User')

    notifications_to_users = relationship('UserNotification', lazy='joined',

                                          cascade="all, delete, delete-orphan")

    @property

    def recipients(self):

        return [x.user for x in UserNotification.query()\

                .filter(UserNotification.notification == self)\

                .order_by(UserNotification.user_id.asc()).all()]

    @classmethod

    def create(cls, created_by, subject, body, recipients, type_=None):

        if type_ is None:

            type_ = Notification.TYPE_MESSAGE

        notification = cls()

        notification.created_by_user = created_by

        notification.subject = subject

        notification.body = body

        notification.type_ = type_

        notification.created_on = datetime.datetime.now()

        for u in recipients:

            assoc = UserNotification()

            assoc.notification = notification

            u.notifications.append(assoc)

        Session().add(notification)

        return notification

    @property

    def description(self):

        from kallithea.model.notification import NotificationModel

        return NotificationModel().make_description(self)

class UserNotification(Base, BaseModel):

    __tablename__ = 'user_to_notification'

    __table_args__ = (

        UniqueConstraint('user_id', 'notification_id'),

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

         'mysql_charset': 'utf8', 'sqlite_autoincrement': True}

    )

    user_id = Column('user_id', Integer(), ForeignKey('users.user_id'), primary_key=True)

    notification_id = Column("notification_id", Integer(), ForeignKey('notifications.notification_id'), primary_key=True)

    read = Column('read', Boolean, default=False)

    sent_on = Column('sent_on', DateTime(timezone=False), nullable=True, unique=None)

    user = relationship('User', lazy="joined")

    notification = relationship('Notification', lazy="joined",

                                order_by=lambda: Notification.created_on.desc(),)

    def mark_as_read(self):

        self.read = True

        Session().add(self)

class Gist(Base, BaseModel):

    __tablename__ = 'gists'

    __table_args__ = (

        Index('g_gist_access_id_idx', 'gist_access_id'),

        Index('g_created_on_idx', 'created_on'),

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

         'mysql_charset': 'utf8', 'sqlite_autoincrement': True}

    )

    GIST_PUBLIC = u'public'

    GIST_PRIVATE = u'private'

    DEFAULT_FILENAME = u'gistfile1.txt'

    gist_id = Column('gist_id', Integer(), primary_key=True)

    gist_access_id = Column('gist_access_id', Unicode(250))

    gist_description = Column('gist_description', UnicodeText(1024))

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

    gist_expires = Column('gist_expires', Float(53), nullable=False)

    gist_type = Column('gist_type', Unicode(128), nullable=False)

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

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

    owner = relationship('User')

    def __repr__(self):

        return '<Gist:[%s]%s>' % (self.gist_type, self.gist_access_id)

    @classmethod

    def get_or_404(cls, id_):

        res = cls.query().filter(cls.gist_access_id == id_).scalar()

        if not res:

            raise HTTPNotFound

        return res

    @classmethod

    def get_by_access_id(cls, gist_access_id):

        return cls.query().filter(cls.gist_access_id == gist_access_id).scalar()

    def gist_url(self):

        import kallithea

        alias_url = kallithea.CONFIG.get('gist_alias_url')

        if alias_url:

            return alias_url.replace('{gistid}', self.gist_access_id)

        import kallithea.lib.helpers as h

        return h.canonical_url('gist', gist_id=self.gist_access_id)

    @classmethod

    def base_path(cls):

        """

        Returns base path when all gists are stored

        :param cls:

        """

        from kallithea.model.gist import GIST_STORE_LOC

        q = Session().query(Ui)\

            .filter(Ui.ui_key == URL_SEP)

        q = q.options(FromCache("sql_cache_short", "repository_repo_path"))

        return os.path.join(q.one().ui_value, GIST_STORE_LOC)

    def get_api_data(self):

        """

        Common function for generating gist related data for API

        """

        gist = self

        data = dict(

            gist_id=gist.gist_id,

            type=gist.gist_type,

            access_id=gist.gist_access_id,

            description=gist.gist_description,

            url=gist.gist_url(),

            expires=gist.gist_expires,

            created_on=gist.created_on,

        )

        return data

    def __json__(self):

        data = dict(

        )

        data.update(self.get_api_data())

        return data

    ## SCM functions

    @property

    def scm_instance(self):

        from kallithea.lib.vcs import get_repo

        base_path = self.base_path()

        return get_repo(os.path.join(*map(safe_str,