_table_args_default_dict,

    )

    HOOK_UPDATE = 'changegroup.update'

    HOOK_REPO_SIZE = 'changegroup.repo_size'

    ui_id = Column(Integer(), primary_key=True)

    ui_section = Column(String(255), nullable=False)

    ui_key = Column(String(255), nullable=False)

    ui_value = Column(String(255), nullable=True) # FIXME: not nullable?

    ui_active = Column(Boolean(), nullable=False, default=True)

    @classmethod

    def get_by_key(cls, section, key):

        """ Return specified Ui object, or None if not found. """

        return cls.query().filter_by(ui_section=section, ui_key=key).scalar()

    @classmethod

    def get_or_create(cls, section, key):

        """ Return specified Ui object, creating it if necessary. """

        setting = cls.get_by_key(section, key)

        if setting is None:

            setting = cls(ui_section=section, ui_key=key)

            Session().add(setting)

        return setting

    @classmethod

    def get_builtin_hooks(cls):

        q = cls.query()

        q = q.filter(cls.ui_key.in_([cls.HOOK_UPDATE, cls.HOOK_REPO_SIZE]))

        q = q.filter(cls.ui_section == 'hooks')

        q = q.order_by(cls.ui_section, cls.ui_key)

        return q.all()

    @classmethod

    def get_custom_hooks(cls):

        q = cls.query()

        q = q.filter(~cls.ui_key.in_([cls.HOOK_UPDATE, cls.HOOK_REPO_SIZE]))

        q = q.filter(cls.ui_section == 'hooks')

        q = q.order_by(cls.ui_section, cls.ui_key)

        return q.all()

    @classmethod

    def get_repos_location(cls):

        return cls.get_by_key('paths', '/').ui_value

    @classmethod

    def create_or_update_hook(cls, key, val):

        new_ui = cls.get_or_create('hooks', key)

        new_ui.ui_active = True

        new_ui.ui_value = val

    def __repr__(self):

        return '<%s %s.%s=%r>' % (

            self.__class__.__name__,

            self.ui_section, self.ui_key, self.ui_value)

class User(Base, BaseDbModel):

    __tablename__ = 'users'

    __table_args__ = (

        Index('u_username_idx', 'username'),

        Index('u_email_idx', 'email'),

        _table_args_default_dict,

    )

    DEFAULT_USER = 'default'

    DEFAULT_GRAVATAR_URL = 'https://secure.gravatar.com/avatar/{md5email}?d=identicon&s={size}'

    # The name of the default auth type in extern_type, 'internal' lives in auth_internal.py

    DEFAULT_AUTH_TYPE = 'internal'

    user_id = Column(Integer(), primary_key=True)

    username = Column(String(255), nullable=False, unique=True)

    password = Column(String(255), nullable=False)

    active = Column(Boolean(), nullable=False, default=True)

    admin = Column(Boolean(), nullable=False, default=False)

    name = Column("firstname", Unicode(255), nullable=False)

    lastname = Column(Unicode(255), nullable=False)

    _email = Column("email", String(255), nullable=True, unique=True) # FIXME: not nullable?

    last_login = Column(DateTime(timezone=False), nullable=True)

    extern_type = Column(String(255), nullable=True) # FIXME: not nullable?

    extern_name = Column(String(255), nullable=True) # FIXME: not nullable?

    api_key = Column(String(255), nullable=False)

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

    _user_data = Column("user_data", LargeBinary(), nullable=True)  # JSON data # FIXME: not nullable?

    user_log = relationship('UserLog')

    user_perms = relationship('UserToPerm', primaryjoin="User.user_id==UserToPerm.user_id", cascade='all')

    repositories = relationship('Repository')

    repo_groups = relationship('RepoGroup')

    user_groups = relationship('UserGroup')

    user_followers = relationship('UserFollowing', primaryjoin='UserFollowing.follows_user_id==User.user_id', cascade='all')

    followings = relationship('UserFollowing', primaryjoin='UserFollowing.user_id==User.user_id', cascade='all')

    repo_to_perm = relationship('UserRepoToPerm', primaryjoin='UserRepoToPerm.user_id==User.user_id', cascade='all')

    repo_group_to_perm = relationship('UserRepoGroupToPerm', primaryjoin='UserRepoGroupToPerm.user_id==User.user_id', cascade='all')

    group_member = relationship('UserGroupMember', cascade='all')

    # comments created by this user

    user_comments = relationship('ChangesetComment', cascade='all')

    # extra emails for this user

    user_emails = relationship('UserEmailMap', cascade='all')

    # extra API keys

    user_api_keys = relationship('UserApiKeys', cascade='all')

    ssh_keys = relationship('UserSshKeys', cascade='all')

    @hybrid_property

    def email(self):

        return self._email

    @email.setter

    def email(self, val):

        self._email = val.lower() if val else None

    @property

    def firstname(self):

        # alias for future

        return self.name

    @property

    def emails(self):

        other = UserEmailMap.query().filter(UserEmailMap.user == self).all()

        return [self.email] + [x.email for x in other]

    @property

    def api_keys(self):

        other = UserApiKeys.query().filter(UserApiKeys.user == self).all()

        return [self.api_key] + [x.api_key for x in other]

    @property

    def ip_addresses(self):

        ret = UserIpMap.query().filter(UserIpMap.user == self).all()

        return [x.ip_addr for x in ret]

    @property

    def full_name(self):

        return '%s %s' % (self.firstname, self.lastname)

    @property

    def full_name_or_username(self):

        """

        Show full name.

        If full name is not set, fall back to username.

        """

        return ('%s %s' % (self.firstname, self.lastname)

                if (self.firstname and self.lastname) else self.username)

    @property

    def full_name_and_username(self):

        """

        Show full name and username as 'Firstname Lastname (username)'.

        If full name is not set, fall back to username.

        """

        return ('%s %s (%s)' % (self.firstname, self.lastname, self.username)

                if (self.firstname and self.lastname) else self.username)

    @property

    def full_contact(self):

        return '%s %s <%s>' % (self.firstname, self.lastname, self.email)

    @property

    def short_contact(self):

        return '%s %s' % (self.firstname, self.lastname)

    @property

    def is_admin(self):

        return self.admin

    @hybrid_property

    def is_default_user(self):

        return self.username == User.DEFAULT_USER

    @hybrid_property

    def user_data(self):

        if not self._user_data:

            return {}

        try:

            return ext_json.loads(self._user_data)

        except TypeError:

            return {}

    @user_data.setter

    def user_data(self, val):

        try:

            self._user_data = ascii_bytes(ext_json.dumps(val))

        except Exception:

            log.error(traceback.format_exc())

    def __repr__(self):

    @classmethod

    def guess_instance(cls, value):

        return super(User, cls).guess_instance(value, User.get_by_username)

    @classmethod

    def get_or_404(cls, id_, allow_default=True):

        '''

        Overridden version of BaseDbModel.get_or_404, with an extra check on

        the default user.

        '''

        user = super(User, cls).get_or_404(id_)

        if not allow_default and user.is_default_user:

            raise DefaultUserException()

        return user

    @classmethod

    def get_by_username_or_email(cls, username_or_email, case_insensitive=True):

        """

        For anything that looks like an email address, look up by the email address (matching

        case insensitively).

        For anything else, try to look up by the user name.

        This assumes no normal username can have '@' symbol.

        """

        if '@' in username_or_email:

            return User.get_by_email(username_or_email)

        else:

            return User.get_by_username(username_or_email, case_insensitive=case_insensitive)

    @classmethod

    def get_by_username(cls, username, case_insensitive=False):

        if case_insensitive:

            q = cls.query().filter(sqlalchemy.func.lower(cls.username) == sqlalchemy.func.lower(username))

        else:

            q = cls.query().filter(cls.username == username)

        return q.scalar()

    @classmethod

    def get_by_api_key(cls, api_key, fallback=True):

        if len(api_key) != 40 or not api_key.isalnum():

            return None

        q = cls.query().filter(cls.api_key == api_key)

        res = q.scalar()

        if fallback and not res:

            # fallback to additional keys

            _res = UserApiKeys.query().filter_by(api_key=api_key, is_expired=False).first()

            if _res:

                res = _res.user

        if res is None or not res.active or res.is_default_user:

            return None

        return res

    @classmethod

    def get_by_email(cls, email, cache=False):

        q = cls.query().filter(sqlalchemy.func.lower(cls.email) == sqlalchemy.func.lower(email))

        ret = q.scalar()

        if ret is None:

            q = UserEmailMap.query()

            # try fetching in alternate email map

            q = q.filter(sqlalchemy.func.lower(UserEmailMap.email) == sqlalchemy.func.lower(email))

            q = q.options(joinedload(UserEmailMap.user))

            ret = getattr(q.scalar(), 'user', None)

        return ret

    @classmethod

    def get_from_cs_author(cls, author):

        """

        Tries to get User objects out of commit author string

        :param author:

        """

        from kallithea.lib.helpers import email, author_name

        # Valid email in the attribute passed, see if they're in the system

        _email = email(author)

        if _email:

            user = cls.get_by_email(_email)

            if user is not None:

                return user

        # Maybe we can match by username?

        _author = author_name(author)

        user = cls.get_by_username(_author, case_insensitive=True)

        if user is not None:

            return user

    def update_lastlogin(self):

        """Update user lastlogin"""

        self.last_login = datetime.datetime.now()

        log.debug('updated user %s lastlogin', self.username)

    @classmethod

    def get_first_admin(cls):

        user = User.query().filter(User.admin == True).first()

        if user is None:

            raise Exception('Missing administrative account!')

        return user

    @classmethod

    def get_default_user(cls):

        user = User.get_by_username(User.DEFAULT_USER)

        if user is None:

            raise Exception('Missing default account!')

        return user

    def get_api_data(self, details=False):

        """

        Common function for generating user related data for API

        """

        user = self

        data = dict(

            user_id=user.user_id,

            username=user.username,

            firstname=user.name,

            lastname=user.lastname,

            email=user.email,

            emails=user.emails,

            active=user.active,

            admin=user.admin,

        )

        if details:

            data.update(dict(

                extern_type=user.extern_type,

                extern_name=user.extern_name,

                api_key=user.api_key,

                api_keys=user.api_keys,

                last_login=user.last_login,

                ip_addresses=user.ip_addresses

                ))

        return data

    def __json__(self):

        data = dict(

            full_name=self.full_name,

            full_name_or_username=self.full_name_or_username,

            short_contact=self.short_contact,

            full_contact=self.full_contact

        )

        data.update(self.get_api_data())

        return data

class UserApiKeys(Base, BaseDbModel):

    __tablename__ = 'user_api_keys'

    __table_args__ = (

        Index('uak_api_key_idx', 'api_key'),

        Index('uak_api_key_expires_idx', 'api_key', 'expires'),

        _table_args_default_dict,

    )

    user_api_key_id = Column(Integer(), primary_key=True)

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

    api_key = Column(String(255), nullable=False, unique=True)

    description = Column(UnicodeText(), nullable=False)

    expires = Column(Float(53), nullable=False)

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

    user = relationship('User')

    @hybrid_property

    def is_expired(self):

        return (self.expires != -1) & (time.time() > self.expires)

class UserEmailMap(Base, BaseDbModel):

    __tablename__ = 'user_email_map'

    __table_args__ = (

        Index('uem_email_idx', 'email'),

        _table_args_default_dict,

    )

    email_id = Column(Integer(), primary_key=True)

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

    _email = Column("email", String(255), nullable=False, unique=True)

    user = relationship('User')

    @validates('_email')

    def validate_email(self, key, email):

        # check if this email is not main one

        main_email = Session().query(User).filter(User.email == email).scalar()

        if main_email is not None:

            raise AttributeError('email %s is present is user table' % email)

        return email

    @hybrid_property

    def email(self):

        return self._email

    @email.setter

    def email(self, val):

        self._email = val.lower() if val else None

class UserIpMap(Base, BaseDbModel):

    __tablename__ = 'user_ip_map'

    __table_args__ = (

        UniqueConstraint('user_id', 'ip_addr'),

        _table_args_default_dict,

    )

    ip_id = Column(Integer(), primary_key=True)

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

    ip_addr = Column(String(255), nullable=False)

    active = Column(Boolean(), nullable=False, default=True)

    user = relationship('User')

    @classmethod

    def _get_ip_range(cls, ip_addr):

        net = ipaddr.IPNetwork(address=ip_addr)

        return [str(net.network), str(net.broadcast)]

    def __json__(self):

        return dict(

          ip_addr=self.ip_addr,

          ip_range=self._get_ip_range(self.ip_addr)

        )

    def __repr__(self):

        return "<%s %s: %s>" % (self.__class__.__name__, self.user_id, self.ip_addr)

class UserLog(Base, BaseDbModel):

    __tablename__ = 'user_logs'

    __table_args__ = (

        _table_args_default_dict,

    )

    user_log_id = Column(Integer(), primary_key=True)

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

    username = Column(String(255), nullable=False)

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

    repository_name = Column(Unicode(255), nullable=False)

    user_ip = Column(String(255), nullable=True)

    action = Column(UnicodeText(), nullable=False)

    action_date = Column(DateTime(timezone=False), nullable=False)

    def __repr__(self):

                                  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, BaseDbModel):

    __tablename__ = 'users_groups'

    __table_args__ = (

        _table_args_default_dict,

    )

    users_group_id = Column(Integer(), primary_key=True)

    users_group_name = Column(Unicode(255), nullable=False, unique=True)

    user_group_description = Column(Unicode(10000), nullable=True) # FIXME: not nullable?

    users_group_active = Column(Boolean(), nullable=False)

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

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

    _group_data = Column("group_data", LargeBinary(), nullable=True)  # JSON data # FIXME: not nullable?

    members = relationship('UserGroupMember', cascade="all, delete-orphan")

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

    owner = relationship('User')

    @hybrid_property

    def group_data(self):

        if not self._group_data:

            return {}

        try:

            return ext_json.loads(self._group_data)

        except TypeError:

            return {}

    @group_data.setter

    def group_data(self, val):

        try:

            self._group_data = ascii_bytes(ext_json.dumps(val))

        except Exception:

            log.error(traceback.format_exc())

    def __repr__(self):

                                  self.users_group_id,

                                  self.users_group_name)

    @classmethod

    def guess_instance(cls, value):

        return super(UserGroup, cls).guess_instance(value, UserGroup.get_by_group_name)

    @classmethod

    def get_by_group_name(cls, group_name, case_insensitive=False):

        if case_insensitive:

            q = cls.query().filter(sqlalchemy.func.lower(cls.users_group_name) == sqlalchemy.func.lower(group_name))

        else:

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

        return q.scalar()

    @classmethod

    def get(cls, user_group_id):

        user_group = cls.query()

        return user_group.get(user_group_id)

    def get_api_data(self, with_members=True):

        user_group = self

        data = dict(

            users_group_id=user_group.users_group_id,

            group_name=user_group.users_group_name,

            group_description=user_group.user_group_description,

            active=user_group.users_group_active,

            owner=user_group.owner.username,

        )

        if with_members:

            data['members'] = [

                ugm.user.get_api_data()

                for ugm in user_group.members

            ]

        return data

class UserGroupMember(Base, BaseDbModel):

    __tablename__ = 'users_groups_members'

    __table_args__ = (

        _table_args_default_dict,

    )

    users_group_member_id = Column(Integer(), primary_key=True)

    users_group_id = Column(Integer(), ForeignKey('users_groups.users_group_id'), nullable=False)

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

    user = relationship('User')

    users_group = relationship('UserGroup')

    def __init__(self, gr_id='', u_id=''):

        self.users_group_id = gr_id

        self.user_id = u_id

class RepositoryField(Base, BaseDbModel):

    __tablename__ = 'repositories_fields'

    __table_args__ = (

        UniqueConstraint('repository_id', 'field_key'),  # no-multi field

        _table_args_default_dict,

    )

    PREFIX = 'ex_'  # prefix used in form to not conflict with already existing fields

    repo_field_id = Column(Integer(), primary_key=True)

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

    field_key = Column(String(250), nullable=False)

    field_label = Column(String(1024), nullable=False)

    field_value = Column(String(10000), nullable=False)

    field_desc = Column(String(1024), nullable=False)

    field_type = Column(String(255), nullable=False)

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

    repository = relationship('Repository')

    @property

    def field_key_prefixed(self):

        return 'ex_%s' % self.field_key

    @classmethod

    def un_prefix_key(cls, key):

        if key.startswith(cls.PREFIX):

            return key[len(cls.PREFIX):]

        return key

    @classmethod

    def get_by_key_name(cls, key, repo):

        row = cls.query() \

                .filter(cls.repository == repo) \

                .filter(cls.field_key == key).scalar()

        return row

class Repository(Base, BaseDbModel):

    __tablename__ = 'repositories'

    __table_args__ = (

        Index('r_repo_name_idx', 'repo_name'),

        _table_args_default_dict,

    )

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

    DEFAULT_CLONE_SSH = 'ssh://{system_user}@{hostname}/{repo}'

    STATE_CREATED = 'repo_state_created'

    STATE_PENDING = 'repo_state_pending'

    STATE_ERROR = 'repo_state_error'

    repo_id = Column(Integer(), primary_key=True)

    repo_name = Column(Unicode(255), nullable=False, unique=True)

    repo_state = Column(String(255), nullable=False)

    clone_uri = Column(String(255), nullable=True) # FIXME: not nullable?

    repo_type = Column(String(255), nullable=False) # 'hg' or 'git'

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

    private = Column(Boolean(), nullable=False)

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

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

    description = Column(Unicode(10000), nullable=False)

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

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

    _landing_revision = Column("landing_revision", String(255), nullable=False)

    _changeset_cache = Column("changeset_cache", LargeBinary(), nullable=True) # JSON data # FIXME: not nullable?

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

    group_id = Column(Integer(), ForeignKey('groups.group_id'), nullable=True)

    owner = 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_repository_id==Repository.repo_id',

                             cascade='all')

    extra_fields = relationship('RepositoryField',

                                cascade="all, delete-orphan")

    logs = relationship('UserLog')

    comments = relationship('ChangesetComment', cascade="all, delete-orphan")

    pull_requests_org = relationship('PullRequest',

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

                    cascade="all, delete-orphan")

    pull_requests_other = relationship('PullRequest',

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

                    cascade="all, delete-orphan")

    def __repr__(self):

        return "<%s %s: %r>" % (self.__class__.__name__,

                                  self.repo_id, 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 changeset_cache(self):

        try:

            cs_cache = ext_json.loads(self._changeset_cache) # might raise on bad data

            cs_cache['raw_id'] # verify data, raise exception on error

            return cs_cache

        except (TypeError, KeyError, ValueError):

            return EmptyChangeset().__json__()

    @changeset_cache.setter

    def changeset_cache(self, val):

        try:

            self._changeset_cache = ascii_bytes(ext_json.dumps(val))

        except Exception:

            log.error(traceback.format_exc())

    @classmethod

    def query(cls, sorted=False):

        """Add Repository-specific helpers for common query constructs.