Changeset - 2b132bc99d6c
Parent rev.
Child rev.
[Not reviewed]
default
0 1 0
Mads Kiilerich - 11 years ago 2015-02-11 02:40:28
madski@unity3d.com
db: drop most lazy joins

Lazy joins will in some cases turn small selects into joins that returns huge
amounts of redundant data - increasingly bad as the repo size increases.

Lazy joins should only be used after careful analysis and with comments
explaining the case.

This change will make some operations faster and probably also make some
operations slower. Performance regressions would have to be analyzed and
addressed separately ... and differently.
1 file changed with 13 insertions and 14 deletions:
kallithea/model/db.py
13
14
0 comments (0 inline, 0 general)
kallithea/model/db.py
➞
Show inline comments
 
@@ -673,47 +673,47 @@ class UserApiKeys(Base, BaseModel):
 
        {'extend_existing': True, 'mysql_engine': 'InnoDB',
 
         'mysql_charset': 'utf8', 'sqlite_autoincrement': True}
 
    )
 
    __mapper_args__ = {}
 

			




	
	
	
		
 
    user_api_key_id = Column("user_api_key_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)

			




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

			




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

			




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

			




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

			




	
	
	
		
 

			




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

			




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

			




	
	
	
		
 
        UniqueConstraint('email'),

			




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

			




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

			




	
	
	
		
 
    )

			




	
	
	
		
 
    __mapper_args__ = {}

			




	
	
	
		
 

			




	
	
	
		
 
    email_id = Column("email_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)

			




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

			




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

			




	
	
	
		
 
    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

			




	
	
		
 
@@ -727,25 +727,25 @@ class UserIpMap(Base, BaseModel):

			




	
	
	
		
 
    __tablename__ = 'user_ip_map'

			




	
	
	
		
 
    __table_args__ = (

			




	
	
	
		
 
        UniqueConstraint('user_id', 'ip_addr'),

			




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

			




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

			




	
	
	
		
 
    )

			




	
	
	
		
 
    __mapper_args__ = {}

			




	
	
	
		
 

			




	
	
	
		
 
    ip_id = Column("ip_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)

			




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

			




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

			




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

			




	
	
	
		
 
    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)

			




	
	
	
		
 
        )

			




	
	
		
 
@@ -789,25 +789,25 @@ class UserGroup(Base, BaseModel):

			




	
	
	
		
 
         '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

			




	
	
	
		
 

			




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

			




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

			




	
	
	
		
 

			




	
	
	
		
 
    @hybrid_property

			




	
	
	
		
 
    def group_data(self):

			




	
	
	
		
 
        if not self._group_data:

			




	
	
	
		
 
            return {}

			




	
	
		
 
@@ -874,25 +874,25 @@ class UserGroup(Base, BaseModel):

			




	
	
	
		
 

			




	
	
	
		
 
class UserGroupMember(Base, BaseModel):

			




	
	
	
		
 
    __tablename__ = 'users_groups_members'

			




	
	
	
		
 
    __table_args__ = (

			




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

			




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

			




	
	
	
		
 
    )

			




	
	
	
		
 

			




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

			




	
	
	
		
 
    users_group_id = Column("users_group_id", Integer(), ForeignKey('users_groups.users_group_id'), nullable=False, unique=None, default=None)

			




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

			




	
	
	
		
 

			




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

			




	
	
	
		
 
    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

			




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

			




	
	
		
 
@@ -1828,25 +1828,25 @@ class UserUserGroupToPerm(Base, BaseMode

			




	
	
	
		
 
class UserToPerm(Base, BaseModel):

			




	
	
	
		
 
    __tablename__ = 'user_to_perm'

			




	
	
	
		
 
    __table_args__ = (

			




	
	
	
		
 
        UniqueConstraint('user_id', 'permission_id'),

			




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

			




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

			




	
	
	
		
 
    )

			




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

			




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

			




	
	
	
		
 
    permission_id = Column("permission_id", Integer(), ForeignKey('permissions.permission_id'), nullable=False, unique=None, default=None)

			




	
	
	
		
 

			




	
	
	
		
 
    user = relationship('User')

			




	
	
	
		
 
    permission = relationship('Permission', lazy='joined')

			




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

			




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

			




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

			




	
	
	
		
 
    )

			




	
	
		
 
@@ -2144,25 +2144,25 @@ class ChangesetComment(Base, BaseModel):

			




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

			




	
	
	
		
 
    author = relationship('User')

			




	
	
	
		
 
    repo = relationship('Repository')

			




	
	
	
		
 
    # status_change is frequently used directly in templates - make it a lazy

			




	
	
	
		
 
    # join to avoid fetching each related ChangesetStatus on demand.

			




	
	
	
		
 
    # There will only be one ChangesetStatus referencing each comment so the join will not explode.

			




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

			




	
	
	
		
 
    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

			




	
	
		
 
@@ -2203,27 +2203,27 @@ class ChangesetStatus(Base, BaseModel):

			




	
	
	
		
 
    ]

			




	
	
	
		
 

			




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

			




	
	
	
		
 
    author = relationship('User')

			




	
	
	
		
 
    repo = relationship('Repository')

			




	
	
	
		
 
    comment = relationship('ChangesetComment', lazy='joined')

			




	
	
	
		
 
    comment = relationship('ChangesetComment')

			




	
	
	
		
 
    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)

			




	
	
	
		
 

			




	
	
		
 
@@ -2265,25 +2265,25 @@ class PullRequest(Base, BaseModel):

			




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

			




	
	
	
		
 
    author = relationship('User')

			




	
	
	
		
 
    reviewers = relationship('PullRequestReviewers',

			




	
	
	
		
 
                             cascade="all, 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-orphan")

			




	
	
	
		
 

			




	
	
	
		
 
    def is_closed(self):

			




	
	
	
		
 
        return self.status == self.STATUS_CLOSED

			




	
	
	
		
 

			




	
	
	
		
 
    @property

			




	
	
		
 
@@ -2343,26 +2343,25 @@ class Notification(Base, BaseModel):

			




	
	
	
		
 
    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-orphan")

			




	
	
	
		
 
    notifications_to_users = relationship('UserNotification', cascade="all, 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

			




	
	
	
		
 

			




	
	
		
 
@@ -2391,26 +2390,26 @@ class Notification(Base, BaseModel):

			




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

			




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

			




	
	
	
		
 
        Index('g_gist_access_id_idx', 'gist_access_id'),

			




	
	
	
		
 
        Index('g_created_on_idx', 'created_on'),

			




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

			




        

    



    


    



    



      

      





    
    0 comments (0 inline, 0 general)
    





    


  

  







  


    




    








    
        
    
    
        This site is powered by
            Kallithea 0.7.0,
        which is
        © 2010–2025 by various authors & licensed under GPLv3.