if id_:

            return cls.query().get(id_)

    @classmethod

    def get_or_404(cls, id_):

        try:

            id_ = int(id_)

        except (TypeError, ValueError):

            raise HTTPNotFound

        res = cls.query().get(id_)

        if res is None:

            raise HTTPNotFound

        return res

    @classmethod

    def getAll(cls):

        # deprecated and left for backward compatibility

        return cls.get_all()

    @classmethod

    def get_all(cls):

        return cls.query().all()

    @classmethod

    def delete(cls, id_):

        obj = cls.query().get(id_)

        Session().delete(obj)

    def __repr__(self):

        if hasattr(self, '__unicode__'):

            # python repr needs to return str

            try:

                return safe_str(self.__unicode__())

            except UnicodeDecodeError:

                pass

        return '<DB:%s>' % (self.__class__.__name__)

_table_args_default_dict = {'extend_existing': True,

                            'mysql_engine': 'InnoDB',

                            'mysql_charset': 'utf8',

                            'sqlite_autoincrement': True,

                           }

class Setting(Base, BaseModel):

    __tablename__ = DB_PREFIX + 'settings'

    __table_args__ = (

        _table_args_default_dict,

    )

    SETTINGS_TYPES = {

        'str': safe_str,

        'int': safe_int,

        'unicode': safe_unicode,

        'bool': str2bool,

        'list': functools.partial(aslist, sep=',')

    }

    DEFAULT_UPDATE_URL = ''

    app_settings_id = Column(Integer(), nullable=False, unique=True, primary_key=True)

    _app_settings_value = Column("app_settings_value", Unicode(4096), nullable=True, unique=None, default=None)

    _app_settings_type = Column("app_settings_type", String(255), nullable=True, unique=None, default=None)

    def __init__(self, key='', val='', type='unicode'):

        self.app_settings_name = key

        self.app_settings_value = val

        self.app_settings_type = type

    @validates('_app_settings_value')

    def validate_settings_value(self, key, val):

        assert type(val) == unicode

        return val

    @hybrid_property

    def app_settings_value(self):

        v = self._app_settings_value

        _type = self.app_settings_type

        converter = self.SETTINGS_TYPES.get(_type) or self.SETTINGS_TYPES['unicode']

        return converter(v)

    @app_settings_value.setter

    def app_settings_value(self, val):

        """

        Setter that will always make sure we use unicode in app_settings_value

        :param val:

        """

        self._app_settings_value = safe_unicode(val)

    @hybrid_property

    def app_settings_type(self):

        return self._app_settings_type

    @app_settings_type.setter

    def app_settings_type(self, val):

        if val not in self.SETTINGS_TYPES:

            raise Exception('type must be one of %s got %s'

                            % (self.SETTINGS_TYPES.keys(), val))

        self._app_settings_type = val

    def __unicode__(self):

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

            self.__class__.__name__,

            self.app_settings_name, self.app_settings_value, self.app_settings_type

        )

    @classmethod

    def get_by_name(cls, key):

    ui_section = Column(String(255), nullable=True, unique=None, default=None)

    ui_key = Column(String(255), nullable=True, unique=None, default=None)

    ui_value = Column(String(255), nullable=True, unique=None, default=None)

    ui_active = Column(Boolean(), nullable=True, unique=None, default=True)

    @classmethod

    def get_by_key(cls, key):

        return cls.query().filter(cls.ui_key == key).scalar()

    @classmethod

    def get_builtin_hooks(cls):

        q = cls.query()

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

                                     cls.HOOK_PUSH, cls.HOOK_PRE_PUSH,

                                     cls.HOOK_PULL, cls.HOOK_PRE_PULL]))

        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,

                                      cls.HOOK_PUSH, cls.HOOK_PRE_PUSH,

                                      cls.HOOK_PULL, cls.HOOK_PRE_PULL]))

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

        return q.all()

    @classmethod

    def get_repos_location(cls):

        return cls.get_by_key('/').ui_value

    @classmethod

    def create_or_update_hook(cls, key, val):

        new_ui = cls.get_by_key(key) or cls()

        new_ui.ui_section = 'hooks'

        new_ui.ui_active = True

        new_ui.ui_key = key

        new_ui.ui_value = val

        Session().add(new_ui)

    def __repr__(self):

        return '<%s[%s]%s=>%s]>' % (self.__class__.__name__, self.ui_section,

                                    self.ui_key, self.ui_value)

class User(Base, BaseModel):

    __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}'

    user_id = Column(Integer(), nullable=False, unique=True, primary_key=True)

    password = Column(String(255), nullable=True, unique=None, default=None)

    active = Column(Boolean(), nullable=True, unique=None, default=True)

    admin = Column(Boolean(), nullable=True, unique=None, default=False)

    name = Column("firstname", Unicode(255), nullable=True, unique=None, default=None)

    lastname = Column(Unicode(255), nullable=True, unique=None, default=None)

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

    extern_type = Column(String(255), nullable=True, unique=None, default=None)

    extern_name = Column(String(255), nullable=True, unique=None, default=None)

    api_key = Column(String(255), nullable=True, unique=None, default=None)

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

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

    _user_data = Column("user_data", LargeBinary(), nullable=True)  # JSON data

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

    notifications = relationship('UserNotification', cascade='all')

    # notifications assigned to this user

    user_created_notifications = relationship('Notification', 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')

    @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):

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

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

    )

    __mapper_args__ = {}

    user_api_key_id = Column(Integer(), nullable=False, unique=True, primary_key=True)

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

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

    description = Column(UnicodeText(1024))

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

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

    user = relationship('User')

    @property

    def expired(self):

        if self.expires == -1:

            return False

        return time.time() > self.expires

class UserEmailMap(Base, BaseModel):

    __tablename__ = 'user_email_map'

    __table_args__ = (

        Index('uem_email_idx', 'email'),

        _table_args_default_dict,

    )

    __mapper_args__ = {}

    email_id = Column(Integer(), nullable=False, unique=True, primary_key=True)

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

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

    __tablename__ = 'user_ip_map'

    __table_args__ = (

        UniqueConstraint('user_id', 'ip_addr'),

        _table_args_default_dict,

    )

    __mapper_args__ = {}

    ip_id = Column(Integer(), nullable=False, unique=True, primary_key=True)

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

    ip_addr = Column(String(255), nullable=True, unique=False, default=None)

    active = Column(Boolean(), nullable=True, unique=None, default=True)

    user = relationship('User')

    @classmethod

    def _get_ip_range(cls, ip_addr):

        from kallithea.lib import ipaddr

        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 __unicode__(self):

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

                                            self.user_id, self.ip_addr)

        n.permission = permission

        Session().add(n)

        return n

class Statistics(Base, BaseModel):

    __tablename__ = 'statistics'

    __table_args__ = (

         _table_args_default_dict,

    )

    stat_id = Column(Integer(), nullable=False, unique=True, primary_key=True)

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

    stat_on_revision = Column(Integer(), nullable=False)

    commit_activity = Column(LargeBinary(1000000), nullable=False)#JSON data

    commit_activity_combined = Column(LargeBinary(), nullable=False)#JSON data

    languages = Column(LargeBinary(1000000), nullable=False)#JSON data

    repository = relationship('Repository', single_parent=True)

class UserFollowing(Base, BaseModel):

    __tablename__ = 'user_followings'

    __table_args__ = (

        UniqueConstraint('user_id', 'follows_repository_id'),

        UniqueConstraint('user_id', 'follows_user_id'),

        _table_args_default_dict,

    )

    user_following_id = Column(Integer(), nullable=False, unique=True, primary_key=True)

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

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

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

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

    user = relationship('User', primaryjoin='User.user_id==UserFollowing.user_id')

    follows_user = relationship('User', primaryjoin='User.user_id==UserFollowing.follows_user_id')

    follows_repository = relationship('Repository', order_by='Repository.repo_name')

    @classmethod

    def get_repo_followers(cls, repo_id):

        return cls.query().filter(cls.follows_repo_id == repo_id)

class CacheInvalidation(Base, BaseModel):

    __tablename__ = 'cache_invalidation'

    __table_args__ = (

        Index('key_idx', 'cache_key'),

        _table_args_default_dict,

    )

    # cache_id, not used

    cache_id = Column(Integer(), nullable=False, unique=True, primary_key=True)

    # cache_key as created by _get_cache_key

    # cache_args is a repo_name

    cache_args = Column(Unicode(255))

    # instance sets cache_active True when it is caching, other instances set

    # cache_active to False to indicate that this cache is invalid

    cache_active = Column(Boolean(), nullable=True, unique=None, default=False)

    def __init__(self, cache_key, repo_name=''):

        self.cache_key = cache_key

        self.cache_args = repo_name

        self.cache_active = False

    def __unicode__(self):

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

            self.__class__.__name__,

            self.cache_id, self.cache_key, self.cache_active)

    def _cache_key_partition(self):

        prefix, repo_name, suffix = self.cache_key.partition(self.cache_args)

        return prefix, repo_name, suffix

    def get_prefix(self):

        """

        get prefix that might have been used in _get_cache_key to

        generate self.cache_key. Only used for informational purposes

        in repo_edit.html.

        """

        # prefix, repo_name, suffix

        return self._cache_key_partition()[0]

    def get_suffix(self):

        """

        get suffix that might have been used in _get_cache_key to

        generate self.cache_key. Only used for informational purposes

        in repo_edit.html.

        """

        # prefix, repo_name, suffix

        return self._cache_key_partition()[2]

    @classmethod

    def clear_cache(cls):

        """

        Delete all cache keys from database.

        Should only be run when all instances are down and all entries thus stale.

        """

        cls.query().delete()

        Session().commit()

    @classmethod