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_name = Column(String(255), nullable=True, unique=True, 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

        UniqueConstraint('ui_section', 'ui_key'),

        _table_args_default_dict,

    )

    HOOK_UPDATE = 'changegroup.update'

    HOOK_REPO_SIZE = 'changegroup.repo_size'

    HOOK_PUSH = 'changegroup.push_logger'

    HOOK_PRE_PUSH = 'prechangegroup.pre_push'

    HOOK_PULL = 'outgoing.pull_logger'

    HOOK_PRE_PULL = 'preoutgoing.pre_pull'

    ui_id = Column(Integer(), nullable=False, unique=True, primary_key=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()

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

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

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

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

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)

    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)

    _email = Column("email", String(255), nullable=True, unique=True, 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

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

class UserLog(Base, BaseModel):

    __tablename__ = 'user_logs'

    __table_args__ = (

        _table_args_default_dict,

    )

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

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

    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__ = (

        _table_args_default_dict,

    )

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

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

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

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

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

    user = relationship('User')

            data['members'] = members

        return data

class UserGroupMember(Base, BaseModel):

    __tablename__ = 'users_groups_members'

    __table_args__ = (

        _table_args_default_dict,

    )

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

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

    __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(), nullable=False, unique=True, primary_key=True)

    field_key = Column(String(250))

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

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

    field_desc = Column(String(1024), 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):]

    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(Integer(), nullable=False, unique=True, primary_key=True)

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

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

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

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

    __tablename__ = 'groups'

    __table_args__ = (

        UniqueConstraint('group_name', 'group_parent_id'),

        CheckConstraint('group_id != group_parent_id'),

        _table_args_default_dict,

    )

    __mapper_args__ = {'order_by': 'group_name'}

    SEP = ' » '

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

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

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

    repo_group_to_perm = relationship('UserRepoGroupToPerm', cascade='all', order_by='UserRepoGroupToPerm.group_to_perm_id')

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

    parent_group = relationship('RepoGroup', remote_side=group_id)

    user = relationship('User')

    def __init__(self, group_name='', parent_group=None):

        self.group_name = group_name

        self.parent_group = parent_group

    def __unicode__(self):

        'hg.usergroup.create.false': 0,

        'hg.usergroup.create.true': 1,

        'hg.fork.none': 0,

        'hg.fork.repository': 1,

        'hg.create.none': 0,

        'hg.create.repository': 1

    }

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

    def __unicode__(self):

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

            self.__class__.__name__, self.permission_id, self.permission_name

        )

    @classmethod

    def get_by_key(cls, key):

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

    @classmethod

    def get_default_perms(cls, default_user_id):

        return q.all()

class UserRepoToPerm(Base, BaseModel):

    __tablename__ = 'repo_to_perm'

    __table_args__ = (

        UniqueConstraint('user_id', 'repository_id', 'permission_id'),

        _table_args_default_dict,

    )

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

    user = relationship('User')

    repository = relationship('Repository')

    permission = relationship('Permission')

    @classmethod

    def create(cls, user, repository, permission):

        n = cls()

        n.user = user

        n.repository = repository

        n.permission = permission

        Session().add(n)

    def __unicode__(self):

        return u'<%s => %s >' % (self.user, self.repository)

class UserUserGroupToPerm(Base, BaseModel):

    __tablename__ = 'user_user_group_to_perm'

    __table_args__ = (

        UniqueConstraint('user_id', 'user_group_id', 'permission_id'),

        _table_args_default_dict,

    )

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

    user = relationship('User')

    user_group = relationship('UserGroup')

    permission = relationship('Permission')

    @classmethod

    def create(cls, user, user_group, permission):

        n = cls()

        n.user = user

        n.user_group = user_group

        n.permission = permission

        Session().add(n)

    def __unicode__(self):

        return u'<%s => %s >' % (self.user, self.user_group)

class UserToPerm(Base, BaseModel):

    __tablename__ = 'user_to_perm'

    __table_args__ = (

        UniqueConstraint('user_id', 'permission_id'),

        _table_args_default_dict,

    )

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

    user = relationship('User')

    permission = relationship('Permission')

    def __unicode__(self):

        return u'<%s => %s >' % (self.user, self.permission)

class UserGroupRepoToPerm(Base, BaseModel):

    __tablename__ = 'users_group_repo_to_perm'

    __table_args__ = (

        UniqueConstraint('repository_id', 'users_group_id', 'permission_id'),

        _table_args_default_dict,

    )

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

    users_group = relationship('UserGroup')

    permission = relationship('Permission')

    repository = relationship('Repository')

    @classmethod

    def create(cls, users_group, repository, permission):

        n = cls()

        n.users_group = users_group

        n.repository = repository

        n.permission = permission

        Session().add(n)

        return u'<UserGroupRepoToPerm:%s => %s >' % (self.users_group, self.repository)

class UserGroupUserGroupToPerm(Base, BaseModel):

    __tablename__ = 'user_group_user_group_to_perm'

    __table_args__ = (

        UniqueConstraint('target_user_group_id', 'user_group_id', 'permission_id'),

        CheckConstraint('target_user_group_id != user_group_id'),

        _table_args_default_dict,

    )

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

    target_user_group = relationship('UserGroup', primaryjoin='UserGroupUserGroupToPerm.target_user_group_id==UserGroup.users_group_id')

    user_group = relationship('UserGroup', primaryjoin='UserGroupUserGroupToPerm.user_group_id==UserGroup.users_group_id')

    permission = relationship('Permission')

    @classmethod

    def create(cls, target_user_group, user_group, permission):

        n = cls()

        n.target_user_group = target_user_group

        n.user_group = user_group

        n.permission = permission

        Session().add(n)

    def __unicode__(self):

        return u'<UserGroupUserGroup:%s => %s >' % (self.target_user_group, self.user_group)

class UserGroupToPerm(Base, BaseModel):

    __tablename__ = 'users_group_to_perm'

    __table_args__ = (

        UniqueConstraint('users_group_id', 'permission_id',),

        _table_args_default_dict,

    )

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

    users_group = relationship('UserGroup')

    permission = relationship('Permission')

class UserRepoGroupToPerm(Base, BaseModel):

    __tablename__ = 'user_repo_group_to_perm'

    __table_args__ = (

        UniqueConstraint('user_id', 'group_id', 'permission_id'),

        _table_args_default_dict,

    )

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

    user = relationship('User')

    group = relationship('RepoGroup')

    permission = relationship('Permission')

    @classmethod

    def create(cls, user, repository_group, permission):

        n = cls()

        n.user = user

        n.group = repository_group

        n.permission = permission

        Session().add(n)

        return n

class UserGroupRepoGroupToPerm(Base, BaseModel):

    __tablename__ = 'users_group_repo_group_to_perm'

    __table_args__ = (

        UniqueConstraint('users_group_id', 'group_id'),

        _table_args_default_dict,

    )

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

    users_group = relationship('UserGroup')

    permission = relationship('Permission')

    group = relationship('RepoGroup')

    @classmethod

    def create(cls, user_group, repository_group, permission):

        n = cls()

        n.users_group = user_group

        n.group = repository_group

        n.permission = permission

        Session().add(n)

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

        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_key = Column(Unicode(255), unique=True)

    # 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

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

    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(Integer(), nullable=False, unique=True, primary_key=True)

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

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

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

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

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

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

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

    author = relationship('User')

    repo = relationship('Repository')

    comment = relationship('ChangesetComment')

    pull_request = relationship('PullRequest')

    )

    # values for .status

    STATUS_NEW = u'new'

    STATUS_CLOSED = u'closed'

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

    title = Column(Unicode(255), nullable=True)

    description = Column(UnicodeText(10240))

    status = Column(Unicode(255), nullable=False, default=STATUS_NEW) # only for closedness, not approve/reject/etc

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

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

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

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

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

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

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

    @hybrid_property

    def revisions(self):

        return self._revisions.split(':')

    @revisions.setter

    def revisions(self, val):

class UserNotification(Base, BaseModel):

    __tablename__ = 'user_to_notification'

    __table_args__ = (

        UniqueConstraint('user_id', 'notification_id'),

        _table_args_default_dict,

    )

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

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

    read = Column(Boolean, default=False)

    user = relationship('User')

    notification = relationship('Notification')

    def mark_as_read(self):

        self.read = True

        Session().add(self)

class Gist(Base, BaseModel):

    __tablename__ = 'gists'

    __table_args__ = (