You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@syncope.apache.org by "Guido Wimmel (JIRA)" <ji...@apache.org> on 2015/05/12 17:17:01 UTC
[jira] [Updated] (SYNCOPE-667) simplification of admin roles filter
query used in search
[ https://issues.apache.org/jira/browse/SYNCOPE-667?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Guido Wimmel updated SYNCOPE-667:
---------------------------------
Description:
As discussed on the dev mailing list [1], I think the query generated in SubjectSearchDAOImpl.getAdminRolesFilter() to determine the users which have roles not in adminRoles can be simplified for type==SubjectType.USER.
Currently generated query: (for type==USER and adminRoles=\{1,2\}):
{code}
SELECT syncopeUser_id AS subject_id FROM Membership M1 WHERE syncopeRole_id IN
(SELECT syncopeRole_id FROM Membership M2 WHERE M2.syncopeUser_id=M1.syncopeUser_id AND syncopeRole_id NOT IN
(SELECT id AS syncopeRole_id FROM SyncopeRole WHERE id=1 OR id=2)
)
{code}
Suggested simplification:
{code}
SELECT syncopeUser_id AS subject_id FROM Membership WHERE syncopeRole_id NOT IN (
SELECT id AS syncopeRole_id FROM SyncopeRole WHERE id=1 OR id=2
)
{code}
In addition, the test coverage of the admin roles filter mechanism used in search should be improved.
[1] http://syncope-dev.1063484.n5.nabble.com/getAdminRolesFilter-query-td5716932.html
was:
As discussed on the dev mailing list [1], I think the query generated in SubjectSearchDAOImpl.getAdminRolesFilter() to determine the users which have roles not in adminRoles can be simplified for type==SubjectType.USER.
Currently generated query: (for type==USER and adminRoles={1,2}):
{code}
SELECT syncopeUser_id AS subject_id FROM Membership M1 WHERE syncopeRole_id IN
(SELECT syncopeRole_id FROM Membership M2 WHERE M2.syncopeUser_id=M1.syncopeUser_id AND syncopeRole_id NOT IN
(SELECT id AS syncopeRole_id FROM SyncopeRole WHERE id=1 OR id=2)
)
{code}
Suggested simplification:
{code}
SELECT syncopeUser_id AS subject_id FROM Membership M2 WHERE syncopeRole_id NOT IN (
SELECT id AS syncopeRole_id FROM SyncopeRole WHERE id=1 OR id=2
)
{code}
In addition, the test coverage of the admin roles filter mechanism used in search should be improved.
[1] http://syncope-dev.1063484.n5.nabble.com/getAdminRolesFilter-query-td5716932.html
> simplification of admin roles filter query used in search
> ---------------------------------------------------------
>
> Key: SYNCOPE-667
> URL: https://issues.apache.org/jira/browse/SYNCOPE-667
> Project: Syncope
> Issue Type: Improvement
> Affects Versions: 1.2.4
> Reporter: Guido Wimmel
> Assignee: Guido Wimmel
> Priority: Minor
>
> As discussed on the dev mailing list [1], I think the query generated in SubjectSearchDAOImpl.getAdminRolesFilter() to determine the users which have roles not in adminRoles can be simplified for type==SubjectType.USER.
> Currently generated query: (for type==USER and adminRoles=\{1,2\}):
> {code}
> SELECT syncopeUser_id AS subject_id FROM Membership M1 WHERE syncopeRole_id IN
> (SELECT syncopeRole_id FROM Membership M2 WHERE M2.syncopeUser_id=M1.syncopeUser_id AND syncopeRole_id NOT IN
> (SELECT id AS syncopeRole_id FROM SyncopeRole WHERE id=1 OR id=2)
> )
> {code}
> Suggested simplification:
> {code}
> SELECT syncopeUser_id AS subject_id FROM Membership WHERE syncopeRole_id NOT IN (
> SELECT id AS syncopeRole_id FROM SyncopeRole WHERE id=1 OR id=2
> )
> {code}
> In addition, the test coverage of the admin roles filter mechanism used in search should be improved.
> [1] http://syncope-dev.1063484.n5.nabble.com/getAdminRolesFilter-query-td5716932.html
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)