On 16/04/2015 11:00, Guido Wimmel wrote:
Hi,
minor detail, but I recently stubled upon the query generated by
SubjectSearchDAOImpl.getAdminRolesFilter(),
used by search() and count(). Maybe it could be simplified.
It seems to determine all users which have roles not in adminRoles (to be able
to filter them out
by WHERE subject_id NOT IN ... ).
The generated query looks like (for type==USER and adminRoles={1,2}):
(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
)
)
I don't really understand why two (inter-dependent) subqueries on membership
are needed.
I don't see a difference to
WHERE subject_id NOT IN (
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
)
)
... but probably I'm overlooking something?
Hi Guido,
this query is generated by SubjectSearchDAOImpl [1] (1_2_X) /
JPASubjectSearchDAO [2] (master) as part of translating the FIQL query
string received via REST into a native SQL query that can work reliably
on all supported RDBMS (MySQL, MariaDB, Oracle, SQL Server, PostgreSQL
and H2).
Naturally, things can always be improved: if you find a way to simplify
the subquery above while keeping everything working an all supported
RDBMS, this is more than welcome!
You can find information about how to check Syncope against all
supported RDBMS at [3]: if you'd like to propose a change but you only
have chance to verify it on a subset (say H2 and MySQL), please create a
separate feature branch (or a pull request on GitHub) and I can help you
completing all verifications needed.
Please let me know.
Regards.
[1]
https://github.com/apache/syncope/blob/1_2_X/core/src/main/java/org/apache/syncope/core/persistence/dao/impl/SubjectSearchDAOImpl.java
[2]
https://github.com/apache/syncope/blob/master/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPASubjectSearchDAO.java
[3] http://syncope.apache.org/building.html#DBMSes
--
Francesco Chicchiriccò
Tirasa - Open Source Excellence
http://www.tirasa.net/
Involved at The Apache Software Foundation:
member, Syncope PMC chair, Cocoon PMC, Olingo PMC
http://people.apache.org/~ilgrosso/
