def __unicode__(self):

        return u"<%s('user_id:%s=>%s')>" % (self.__class__.__name__,

                                            self.user_id, self.ip_addr)

class UserLog(Base, BaseModel):

    __tablename__ = 'user_logs'

    __table_args__ = (

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

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

    )

    user_log_id = Column("user_log_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)

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

    username = Column("username", String(255, convert_unicode=False), nullable=True, unique=None, default=None)

    repository_id = Column("repository_id", Integer(), ForeignKey('repositories.repo_id'), nullable=True)

    repository_name = Column("repository_name", String(255, convert_unicode=False), nullable=True, unique=None, default=None)

    user_ip = Column("user_ip", String(255, convert_unicode=False), nullable=True, unique=None, default=None)

    action = Column("action", UnicodeText(1200000, convert_unicode=False), nullable=True, unique=None, default=None)

    action_date = Column("action_date", DateTime(timezone=False), nullable=True, unique=None, default=None)

    def __unicode__(self):

        return u"<%s('id:%s:%s')>" % (self.__class__.__name__,

                                      self.repository_name,

                                      self.action)

    @property

    def action_as_day(self):

        return datetime.date(*self.action_date.timetuple()[:3])

    user = relationship('User')

    repository = relationship('Repository', cascade='')

class UserGroup(Base, BaseModel):

    __tablename__ = 'users_groups'

    __table_args__ = (

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

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

    )

    users_group_id = Column("users_group_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)

    users_group_name = Column("users_group_name", String(255, convert_unicode=False), nullable=False, unique=True, default=None)

    user_group_description = Column("user_group_description", String(10000, convert_unicode=False), nullable=True, unique=None, default=None)

    users_group_active = Column("users_group_active", Boolean(), nullable=True, unique=None, default=None)

    inherit_default_permissions = Column("users_group_inherit_default_permissions", Boolean(), nullable=False, unique=None, default=True)

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

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

    _group_data = Column("group_data", LargeBinary(), nullable=True)  # JSON data

    users_group_to_perm = relationship('UserGroupToPerm', cascade='all')

    users_group_repo_to_perm = relationship('UserGroupRepoToPerm', cascade='all')

    users_group_repo_group_to_perm = relationship('UserGroupRepoGroupToPerm', cascade='all')

    user_user_group_to_perm = relationship('UserUserGroupToPerm ', cascade='all')

    user_group_user_group_to_perm = relationship('UserGroupUserGroupToPerm ', primaryjoin="UserGroupUserGroupToPerm.target_user_group_id==UserGroup.users_group_id", cascade='all')

    user = relationship('User')

    @hybrid_property

    def group_data(self):

        if not self._group_data:

            return {}

        try:

            return json.loads(self._group_data)

        except TypeError:

            return {}

    @group_data.setter

    def group_data(self, val):

        try:

            self._group_data = json.dumps(val)

        except Exception:

            log.error(traceback.format_exc())

    def __unicode__(self):

        return u"<%s('id:%s:%s')>" % (self.__class__.__name__,

                                      self.users_group_id,

                                      self.users_group_name)

    @classmethod

    def get_by_group_name(cls, group_name, cache=False,

                          case_insensitive=False):

        if case_insensitive:

            q = cls.query().filter(cls.users_group_name.ilike(group_name))

        else:

            q = cls.query().filter(cls.users_group_name == group_name)

        if cache:

            q = q.options(FromCache(

                            "sql_cache_short",

                            "get_user_%s" % _hash_key(group_name)

                          )

            )

        return q.scalar()

    @classmethod

    def get(cls, user_group_id, cache=False):

        user_group = cls.query()

class Repository(Base, BaseModel):

    __tablename__ = 'repositories'

    __table_args__ = (

        UniqueConstraint('repo_name'),

        Index('r_repo_name_idx', 'repo_name'),

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

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

    )

    DEFAULT_CLONE_URI = '{scheme}://{user}@{netloc}/{repo}'

    DEFAULT_CLONE_URI_ID = '{scheme}://{user}@{netloc}/_{repoid}'

    STATE_CREATED = 'repo_state_created'

    STATE_PENDING = 'repo_state_pending'

    STATE_ERROR = 'repo_state_error'

    repo_id = Column("repo_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)

    repo_name = Column("repo_name", String(255, convert_unicode=False), nullable=False, unique=True, default=None)

    repo_state = Column("repo_state", String(255), nullable=True)

    clone_uri = Column("clone_uri", String(255, convert_unicode=False), nullable=True, unique=False, default=None)

    repo_type = Column("repo_type", String(255, convert_unicode=False), nullable=False, unique=False, default=None)

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

    private = Column("private", Boolean(), nullable=True, unique=None, default=None)

    enable_statistics = Column("statistics", Boolean(), nullable=True, unique=None, default=True)

    enable_downloads = Column("downloads", Boolean(), nullable=True, unique=None, default=True)

    description = Column("description", String(10000, convert_unicode=False), nullable=True, unique=None, default=None)

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

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

    _landing_revision = Column("landing_revision", String(255, convert_unicode=False), nullable=False, unique=False, default=None)

    enable_locking = Column("enable_locking", Boolean(), nullable=False, unique=None, default=False)

    _locked = Column("locked", String(255, convert_unicode=False), nullable=True, unique=False, default=None)

    _changeset_cache = Column("changeset_cache", LargeBinary(), nullable=True) #JSON data

    fork_id = Column("fork_id", Integer(), ForeignKey('repositories.repo_id'), nullable=True, unique=False, default=None)

    group_id = Column("group_id", Integer(), ForeignKey('groups.group_id'), nullable=True, unique=False, default=None)

    user = relationship('User')

    fork = relationship('Repository', remote_side=repo_id)

    group = relationship('RepoGroup')

    repo_to_perm = relationship('UserRepoToPerm', cascade='all', order_by='UserRepoToPerm.repo_to_perm_id')

    users_group_to_perm = relationship('UserGroupRepoToPerm', cascade='all')

    stats = relationship('Statistics', cascade='all', uselist=False)

    followers = relationship('UserFollowing',

                             primaryjoin='UserFollowing.follows_repo_id==Repository.repo_id',

                             cascade='all')

    extra_fields = relationship('RepositoryField',

    logs = relationship('UserLog')

    pull_requests_org = relationship('PullRequest',

                    primaryjoin='PullRequest.org_repo_id==Repository.repo_id',

    pull_requests_other = relationship('PullRequest',

                    primaryjoin='PullRequest.other_repo_id==Repository.repo_id',

    def __unicode__(self):

        return u"<%s('%s:%s')>" % (self.__class__.__name__, self.repo_id,

                                   safe_unicode(self.repo_name))

    @hybrid_property

    def landing_rev(self):

        # always should return [rev_type, rev]

        if self._landing_revision:

            _rev_info = self._landing_revision.split(':')

            if len(_rev_info) < 2:

                _rev_info.insert(0, 'rev')

            return [_rev_info[0], _rev_info[1]]

        return [None, None]

    @landing_rev.setter

    def landing_rev(self, val):

        if ':' not in val:

            raise ValueError('value must be delimited with `:` and consist '

                             'of <rev_type>:<rev>, got %s instead' % val)

        self._landing_revision = val

    @hybrid_property

    def locked(self):

        # always should return [user_id, timelocked]

        if self._locked:

            _lock_info = self._locked.split(':')

            return int(_lock_info[0]), _lock_info[1]

        return [None, None]

    @locked.setter

    def locked(self, val):

        if val and isinstance(val, (list, tuple)):

            self._locked = ':'.join(map(str, val))

        else:

            self._locked = None

    @hybrid_property

    def changeset_cache(self):

        from kallithea.lib.vcs.backends.base import EmptyChangeset

        dummy = EmptyChangeset().__json__()

        if not self._changeset_cache:

            return dummy

        try:

            return json.loads(self._changeset_cache)

        except TypeError:

            return dummy

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

    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)

    @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 = safe_unicode(':'.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',

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

    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

        b = self.org_ref_parts[1]

        if b != self.other_ref_parts[1]:

            s = '/_/' + b

        else:

            s = '/_/' + self.title

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

    @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(),)