% 489590 - Updated query to retrieve configuration managed systems to check
that the systems returned have provisioning entitlements.
%
% • [DH] java/code/src/com/redhat/rhn/common/db/datasource/xml/
% config_queries.xml
%
% URL: http://git.fedoraproject.org/git/?p=spacewalk.git;a=commitdiff;h=
% fb66c0dcc377f8d789a31e829eeeea7de97b7120
+SELECT DISTINCT S.id,
+ S.name
+ FROM rhnServer S
+INNER JOIN rhnUserServerPerms USP on S.id = USP.server_id
+INNER JOIN rhnClientCapability CC on S.id = CC.server_id
+INNER JOIN rhnClientCapabilityName CCN on CC.capability_name_id = CCN.id
+ WHERE USP.user_id = :user_id
+ AND CCN.name LIKE 'configfiles%'
+ AND S.id in (
+ SELECT SS.id
+ FROM rhnServer SS,
+ rhnServerGroupMembers MM,
+ rhnServerGroup GG,
+ rhnServerGroupType TT
+ WHERE SS.id = MM.server_id
+ AND MM.server_group_id = GG.id
+ AND GG.group_type = TT.id
+ AND TT.label = 'provisioning_entitled'
+ )
+ ORDER BY UPPER(NVL(S.name, '(none)')), S.id
The 'rhnServer SS' table in subselect is redundant :). As I can see the only
reason to have it there is join SS.id = MM.server_id and then return SS.id,
so why not return MM.server_id directly? It will save us one table scan
(well, its primary key index in fact) and one table join...
So modified version could be
SELECT DISTINCT S.id,
S.name
FROM rhnServer S
INNER JOIN rhnUserServerPerms USP on S.id = USP.server_id
INNER JOIN rhnClientCapability CC on S.id = CC.server_id
INNER JOIN rhnClientCapabilityName CCN on CC.capability_name_id = CCN.id
WHERE USP.user_id = :user_id
AND CCN.name LIKE 'configfiles%'
AND S.id in (
SELECT MM.server_id
rhnServerGroupMembers MM,
rhnServerGroup GG,
rhnServerGroupType TT
WHERE MM.server_group_id = GG.id
AND GG.group_type = TT.id
AND TT.label = 'provisioning_entitled'
)
ORDER BY UPPER(NVL(S.name, '(none)')), S.id
--
Michael Mráka
Satellite Engineering, Red Hat
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
