java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml | 66 ++++------
1 file changed, 31 insertions(+), 35 deletions(-)
New commits:
commit d50e60bc201b11c7b23c7f7eb06f6a58ee593fb0
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Mon Dec 5 17:44:38 2011 +0100
speeded up query
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml
index e8ea52b..387cb68 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml
@@ -2097,8 +2097,8 @@ ORDER BY UPPER(CC.name)
<elaborator name="ssm_channel_elab" />
</mode>
-<!-- Returns the list of configuration channels that *every* server currently in the SSM
- are subscribed to. The idea here is that these will need to be taken into account
+<!-- Returns the list of configuration channels that at least one server currently in the SSM
+ is subscribed to. The idea here is that these will need to be taken into account
when ranking newly subscribed channels, so these must be added to the user selection
(they are not already in the user selection because they were omitted as effectively
a no-op since the subscriptions exist.
@@ -2109,19 +2109,21 @@ ORDER BY UPPER(CC.name)
<mode name="ssm_channels_for_subscribe_already_sub" class="com.redhat.rhn.frontend.dto.ConfigChannelDto">
<query params="org_id, user_id">
SELECT CC.id, CC.name
- FROM rhnConfigChannel CC, rhnConfigChannelType CCT
+ FROM rhnConfigChannel CC
+ JOIN rhnConfigChannelType CCT
+ ON CC.confchan_type_id = CCT.id
+ LEFT JOIN (SELECT DISTINCT SCC.config_channel_id
+ FROM rhnSet RS
+ LEFT JOIN rhnServerConfigChannel SCC
+ ON RS.element = SCC.server_id
+ WHERE RS.label = 'system_list'
+ AND RS.user_id = :user_id
+ ) SSMCHannels
+ ON CC.id = SSMCHannels.config_channel_id
WHERE CC.org_id = :org_id
- AND CC.confchan_type_id = CCT.id
AND rhn_config_channel.get_user_chan_access(CC.id, :user_id) = 1
AND CCT.label = 'normal'
- AND CC.id IN ( SELECT XXX.config_channel_id
- FROM rhnServerConfigChannel XXX
- WHERE NOT EXISTS ( SELECT RS.element
- FROM rhnSet RS LEFT OUTER JOIN rhnServerConfigChannel SCC
- ON RS.element = SCC.server_id
- WHERE RS.label = 'system_list'
- AND SCC.config_channel_id is null
- AND SCC.server_id is null) )
+ AND SSMCHannels.config_channel_id is not NULL
ORDER BY UPPER(CC.name)
</query>
<elaborator name="ssm_channel_elab" />
commit a0ac26914ef0c80202dc0d707966ec34f523053e
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Mon Dec 5 17:20:46 2011 +0100
fixed list of config channels relevant to SSM
query was broken since 3f95a42cb3640370579ef81d52157c1e53cf2fb5
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml
index 3e6784e..e8ea52b 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml
@@ -2068,36 +2068,30 @@ SELECT CC.id,
<!-- Returns the list of configuration channels to display as options for SSM server to
subscribe to. This query will *not* display channels that every server in the SSM
is already subscribed to since it would be redundant.
-
- Since recent changes have made this query much more complex, below is a quick
- explanation of how it functions.
-
- - For each config channel, take the set difference (MINUS) of the servers selected the
- SSM and the servers subscribed to that channel. The result represents the servers
- in the SSM that still may want to subscribe to that channel.
- - The last query (the last AND) in the outer query returns all channels where there
- are no servers that may be subscribed to the channel (i.e. when there are no servers
- returned from the above difference; WHERE NOT EXISTS). These channels should *not*
- be shown to the user since they will effectively be a no-op on a subscribe operation.
- - Return all config channels that the user has permissions to that are not part of
- the above channel exclusions.
-->
<mode name="ssm_channels_for_subscribe_choose" class="com.redhat.rhn.frontend.dto.ConfigChannelDto">
<query params="org_id, user_id">
SELECT CC.id, CC.name
- FROM rhnConfigChannel CC, rhnConfigChannelType CCT
+ FROM rhnConfigChannel CC
+ JOIN rhnConfigChannelType CCT
+ ON CC.confchan_type_id = CCT.id
+ LEFT JOIN (SELECT count(RS.element) server_cnt,
+ SCC.config_channel_id
+ FROM rhnSet RS
+ LEFT JOIN rhnServerConfigChannel SCC
+ ON RS.element = SCC.server_id
+ WHERE RS.label = 'system_list'
+ AND RS.user_id = :user_id
+ GROUP BY SCC.config_channel_id
+ ) SSMCHannels
+ ON CC.id = SSMCHannels.config_channel_id
WHERE CC.org_id = :org_id
- AND CC.confchan_type_id = CCT.id
AND rhn_config_channel.get_user_chan_access(CC.id, :user_id) = 1
AND CCT.label = 'normal'
- AND CC.id NOT IN ( SELECT XXX.config_channel_id
- FROM rhnServerConfigChannel XXX
- WHERE NOT EXISTS ( SELECT RS.element
- FROM rhnSet RS LEFT OUTER JOIN rhnServerConfigChannel SCC
- ON RS.element = SCC.server_id
- WHERE RS.label = 'system_list'
- AND SCC.config_channel_id is null
- AND SCC.server_id is null) )
+ AND COALESCE(SSMCHannels.server_cnt, 0) < (SELECT count(*)
+ FROM rhnSet
+ WHERE label = 'system_list'
+ AND user_id = :user_id)
ORDER BY UPPER(CC.name)
</query>
<elaborator name="ssm_channel_elab" />