[prev in list] [next in list] [prev in thread] [next in thread] 

List:       spacewalk-devel
Subject:    [Spacewalk-devel] Re: 489590 - Updated query to retrieve	configuration managed systems to check that
From:       jason.dobies () redhat ! com (Jason Dobies)
Date:       2009-03-23 15:26:38
Message-ID: 49C7AA2E.8070202 () redhat ! com
[Download RAW message or body]

Michael Mraka wrote:
> % 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 at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-devel

This is why these sorts of quick informal reviews rock, it's amazing 
what a fresh pair of eyes can see.

Thanks, will incorporate shortly  :)

-- 
Jason Dobies
RHN Satellite / Spacewalk
RHCE# 805008743336126
Freenode: jdob @ #spacewalk #spacewalk-devel


[prev in list] [next in list] [prev in thread] [next in thread]