Feature #34044

Potential better SQL query in extra/svn/Redmine.pm

Added by Toru HARAGUCHI over 1 year ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:SCM extra
Target version:-
Resolution:

Description

Due to lack of attention, extra/svn/Redmine.pm is ditched from recent improvements to Redmine such as acquisition of identifier in repositories and allowance to register multiple repositories in a project.
As so, the SQL query in Redmine.pm is still referring projects.identifier to identify the repo which is being fetched through HTTP, and this becomes hidden limitation to have multiple repos in a project.

*** original/extra/svn/Redmine.pm    2020-04-07 02:00:47.000000000 +0900
--- modified/extra/svn/Redmine.pm    2020-09-29 10:21:11.504258505 +0900
***************
*** 239,262 ****
    my ($self, $parms, $arg) = @_;
    $self->{RedmineDSN} = $arg;
    my $query = "SELECT 
!                  users.hashed_password, users.salt, users.auth_source_id, roles.permissions, projects.status
!               FROM projects, users, roles
                WHERE 
                  users.login=? 
!                 AND projects.identifier=?
                  AND EXISTS (SELECT 1 FROM enabled_modules em WHERE em.project_id = projects.id AND em.name = 'repository')
                  AND users.type='User'
                  AND users.status=1 
                  AND (
!                   roles.id IN (SELECT member_roles.role_id FROM members, member_roles WHERE members.user_id = users.id AND members.project_id = projects.id AND members.id = member_roles.member_id)
                    OR
                    (cast(projects.is_public as CHAR) IN ('t', '1')
                      AND (roles.builtin=1
!                          OR roles.id IN (SELECT member_roles.role_id FROM members, member_roles, users g
!                                  WHERE members.user_id = g.id AND members.project_id = projects.id AND members.id = member_roles.member_id
!                                  AND g.type = 'GroupNonMember'))
                    )
                  )
                  AND roles.permissions IS NOT NULL";
    $self->{RedmineQuery} = trim($query);
  }
--- 239,264 ----
    my ($self, $parms, $arg) = @_;
    $self->{RedmineDSN} = $arg;
    my $query = "SELECT 
!                 users.hashed_password, users.salt, users.auth_source_id, roles.permissions, projects.status
!               FROM projects, users, roles, repositories
                WHERE 
                  users.login=? 
!                 AND repositories.identifier=LOWER(?)
                  AND EXISTS (SELECT 1 FROM enabled_modules em WHERE em.project_id = projects.id AND em.name = 'repository')
                  AND users.type='User'
                  AND users.status=1 
                  AND (
!                   roles.id IN (SELECT member_roles.role_id FROM members, member_roles WHERE members.user_id = users.id 
!                                 AND members.project_id = projects.id AND members.id = member_roles.member_id)
                    OR
                    (cast(projects.is_public as CHAR) IN ('t', '1')
                      AND (roles.builtin=1
!                         OR roles.id IN (SELECT member_roles.role_id FROM members, member_roles, users g
!                                 WHERE members.user_id = g.id AND members.project_id = projects.id AND members.id = member_roles.member_id
!                                 AND g.type = 'GroupNonMember'))
                    )
                  )
+                 AND repositories.project_id=projects.id
                  AND roles.permissions IS NOT NULL";
    $self->{RedmineQuery} = trim($query);
  }
***************

Also available in: Atom PDF