[Spacewalk-list] Modification of PGSQL queries
Pierre Casenove
pcasenove at gmail.com
Wed Aug 10 15:10:45 UTC 2011
Hello,
One last modification:
In git 60ca30539dd924f4e2ffd2b0e0a8a27bb0e8b174 , the NVL2 keyword is
replaced by NVL in config_queries.xml.
But there are still "(+)" joins performed.
Please find attached a diff -u of config_queries.xml that corrects the last
two requests.
Thanks in advance for the review and commit in master
Pierre
2011/8/10 Pierre Casenove <pcasenove at gmail.com>
> I have a doubt on one commit:
> On git f6d4609327644d01a522465cd33022a052ff664c there is a closing
> parenthesis on line 48, after END keyword.
> I think it is a mistake: if you count, there are now two opening
> parenthesis and three closing before the FROM keyword.
>
> 32 SELECT SECURITY_ERRATA, BUG_ERRATA, ENHANCEMENT_ERRATA,
> 33 GO.GROUP_ID AS ID, GROUP_NAME, GROUP_ADMINS, SERVER_COUNT,
> 34 NOTE_COUNT, GO.MODIFIED, GO.MAX_MEMBERS,
> 35 'unknown' AS ALL_ERRATA,
> 36 (SELECT CASE MAX(CASE PS.state
> 37 WHEN 'OK' THEN 1
> 38 WHEN 'PENDING' THEN 2
> 39 WHEN 'UNKNOWN' THEN 3
> 40 WHEN 'WARNING' THEN 4
> 41 WHEN 'CRITICAL' THEN 5
> 42 END)
> 43 WHEN 1 THEN 'OK'
> 44 WHEN 2 THEN 'PENDING'
> 45 WHEN 3 THEN 'UNKNOWN'
> 46 WHEN 4 THEN 'WARNING'
> 47 WHEN 5 THEN 'CRITICAL'
> 48 END) ST
> 49 FROM rhn_probe_state PS, rhnServerGroupMembership SGM
> 50 LEFT JOIN rhn_check_probe CP
> 51 ON SGM.server_id = CP.host_id
> 52 WHERE PS.probe_id = CP.probe_id
> 53 AND SGM.group_id = UMSG.server_group_id
> 54 GROUP BY SGM.group_id) MONITORING_STATUS
> 55 FROM rhnVisServerGroupOverviewLite GO, rhnUserManagedServerGroups
> UMSG
> 56 WHERE GO.ORG_ID = :org_id
> 57 AND UMSG.user_id = :user_id
> 58 AND UMSG.server_group_id IN (%s)
> 59 AND GO.GROUP_ID = UMSG.server_group_id
> 60 ORDER BY UPPER(GROUP_NAME), GO.GROUP_ID
>
> 2011/8/10 Michael Mraka <michael.mraka at redhat.com>
>
>> Pierre Casenove wrote:
>> % Hello,
>> % Could you please review my modified queries?
>> %
>> % I also ran into another error 500 pgsql on page
>> % /network/software/packages/target_system_list.pxt
>> % I've modified query "potential_systems_for_package" in file
>> %
>> /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/DataSource/xml/System_queries.xml
>> % and replace the NVL keyword by coalesce:
>> % SELECT S.id
>> % FROM rhnPackage P, rhnChannelPackage CP, rhnServerChannel SC,
>> rhnServer S
>> % WHERE S.org_id = :org_id
>> % AND SC.server_id = S.id
>> % AND SC.channel_id = CP.channel_id
>> % AND CP.package_id = :pid
>> % AND CP.package_id = P.id
>> % AND COALESCE((SELECT MAX(PE.evr)
>> % FROM rhnServerPackage SP, rhnPackageEvr PE
>> % WHERE SP.name_id = P.name_id
>> % AND SP.server_id = S.id
>> % AND SP.evr_id = PE.id), (EVR_T(NULL, '0', '0'))
>> % )
>> % <
>> % (SELECT EVR FROM rhnPackageEVR PE WHERE PE.id = P.evr_id)
>> % AND EXISTS (SELECT 1 FROM rhnUserServerPerms USP WHERE USP.user_id
>> =
>> % :user_id AND USP.server_id = S.id)
>> % ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
>>
>> Pierre please send the changes as patches (diffs). From the outout above
>> it's hard to guess what the actual change is.
>>
>> I've commited NVL->COALESCE change to master. New package
>> (spacewalk-base-1.6.11-1) should be available in a moment.
>>
>> % Thanks in advance for the review.
>> %
>> % Pierre
>>
>> Regards,
>>
>> --
>> Michael Mráka
>> Satellite Engineering, Red Hat
>>
>> _______________________________________________
>> Spacewalk-list mailing list
>> Spacewalk-list at redhat.com
>> https://www.redhat.com/mailman/listinfo/spacewalk-list
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20110810/268f2092/attachment.htm>
-------------- next part --------------
diff -u config_queries.xml /root/config_queries.orig
--- config_queries.xml 2011-08-10 15:06:45.127110641 +0000
+++ /root/config_queries.orig 2011-08-10 15:05:26.195823010 +0000
@@ -18,24 +18,24 @@
CFT.name as FILETYPE
FROM rhnConfigChannelType CCT,
rhnConfigChannel CC,
+ rhnConfigContent CCon,
rhnConfigFileName CFN,
rhnConfigFile CF,
+ rhnConfigRevision CR,
rhnServerConfigChannel SCC,
rhnConfigFileType CFT,
- rhnConfigRevision CR
- LEFT JOIN rhnConfigContent CCon
- ON CR.config_content_id = CCon.id
- LEFT JOIN rhnChecksum Csum
- ON CCon.checksum_id = Csum.id
+ rhnChecksum Csum
WHERE SCC.server_id = :sid
AND SCC.config_channel_id = CF.config_channel_id
AND CF.id = CR.config_file_id
AND CF.config_file_name_id = CFN.id
AND CF.config_channel_id = CC.id
AND CC.confchan_type_id = CCT.id
+ AND CR.config_content_id = CCon.id (+)
AND CF.latest_config_revision_id = CR.id
AND CCT.label in ('normal', 'local_override')
AND CR.config_file_type_id = CFT.id
+ AND CCon.checksum_id = Csum.id(+)
ORDER BY CCT.priority, SCC.position
</query>
<elaborator params="sid" multiple="t">
@@ -151,14 +151,13 @@
<query params="sid, aid">
SELECT CFN.path,
CFF.name AS FAILURE_REASON
- FROM
+ FROM rhnConfigFileFailure CFF,
rhnConfigFileName CFN,
rhnActionConfigFileName ACFN
- LEFT JOIN rhnConfigFileFailure CFF
- ON ACFN.failure_id = CFF.id
WHERE ACFN.server_id = :sid
AND ACFN.action_id = :aid
AND ACFN.config_file_name_id = CFN.id
+ AND ACFN.failure_id = CFF.id (+)
ORDER BY CFN.path
</query>
</mode>
More information about the Spacewalk-list
mailing list