[Spacewalk-list] Modification of PGSQL queries

[Pierre Casenove]

Hello,
After getting rid of hotmail, I'll try to send well formatted mail from
gmail...
So, I've continued to modify queries having (+) in them to get them PGSQL
compatible.
I attach the diff output. i've tested the queries from psql command line as
I didn't found where there were used in spacewalk.
Here are the modified queries:
contact_method_queries.xml : query "orgs_contact_method_tree"
CustomInfo_queries.xml : query "custom_info_keys_sans_value_for_system"
SystemGroup_queries.xml : query "visible_to_user_overview_fast" and
query "visible_groups_summary"
probe_queries.xml : query "system_probes"


Here are the queries I can't find how to modify:
config_queries.xml : query "configfiles_for_system"
The keywork NVL2 is used, but when I replace it by COALESCE keyword, I get:
ERROR:  invalid input syntax for integer: "Y"
LINE 5: ...CFt WHERE CFT.latest_config_revision_id = CR.id), 'Y', 'N') ...

Package_queries.xml : query "system_upgradable_package_list":
When I put LEFT JOIN in the query, it crash with error
ERROR:  syntax error at or near "VERSION"
LINE 9:         full_list.evr.version VERSION,
If I replace "VERSION" by "FOO", it crashes with error
ERROR:  schema "full_list" does not exist

I hope this helps.
Please let me know if the changes are commited too the master branch.

Could you please help me on the last two queries I can't get to work? I
can't find anything in the postgresql porting guide.

I hope my email will be readable.

Pierre
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20110803/f6cbee4b/attachment.htm>
-------------- next part --------------
diff contact_method_queries.xml /root/contact_method_queries.xml.orig
31,38c31,36
<        CASE MT.method_type_name
<          WHEN 'Email' THEN CM.email_address
<          WHEN 'Pager' THEN CM.pager_email END method_target
<   FROM web_contact WC
<   RIGHT JOIN rhn_contact_methods CM
<   ON CM.contact_id  = WC.id
<   LEFT JOIN rhn_method_types MT
<   ON MT.recid = CM.method_type_id
---
>        DECODE( MT.method_type_name,
>          'Email', CM.email_address,
>          'Pager', CM.pager_email) method_target
>   FROM rhn_contact_methods CM,
>        web_contact WC,
>        rhn_method_types MT
39a38,39
>    AND CM.contact_id (+) = WC.id
>    AND CM.method_type_id = MT.recid (+)



diff ./CustomInfo_queries.xml /root/CustomInfo_queries.xml.orig
30,32c30,31
<   FROM rhnCustomDataKey CDK
<   LEFT JOIN rhnServerCustomDataValue SCDV
<     ON CDK.id = SCDV.key_id
---
>   FROM rhnServerCustomDataValue SCDV,
>        rhnCustomDataKey CDK
33a33
>    AND CDK.id = SCDV.key_id (+)



diff SystemGroup_queries.xml /root/SystemGroup_queries.xml.orig
37,52c37,42
<          (SELECT CASE MAX(CASE PS.state
<               WHEN 'OK' THEN 1
<               WHEN 'PENDING' THEN 2
<               WHEN 'UNKNOWN' THEN 3
<               WHEN 'WARNING' THEN 4
<               WHEN 'CRITICAL' THEN 5 END)
<           WHEN 1 THEN 'OK'
<           WHEN 2 THEN 'PENDING'
<           WHEN 3 THEN 'UNKNOWN'
<           WHEN 4 THEN 'WARNING'
<           WHEN 5 THEN 'CRITICAL' END ST
<            FROM  rhn_probe_state PS,
<                rhnServerGroupMembership SGM
<            LEFT JOIN rhn_check_probe CP
<            ON SGM.server_id = CP.host_id
<            WHERE  PS.probe_id = CP.probe_id
---
>          (SELECT  DECODE(
>                   MAX(DECODE(PS.state, 'OK', 1, 'PENDING', 2, 'UNKNOWN', 3, 'WARNING', 4, 'CRITICAL', 5)),
>                       1, 'OK', 2, 'PENDING', 3, 'UNKNOWN', 4, 'WARNING', 5, 'CRITICAL') ST
>            FROM  rhn_check_probe CP, rhn_probe_state PS, rhnServerGroupMembership SGM
>           WHERE  PS.probe_id = CP.probe_id
>             AND  SGM.server_id = CP.host_id(+)
83,97c73,76
<          (SELECT CASE MAX(CASE PS.state
<                 WHEN 'OK' THEN 1
<                 WHEN 'PENDING' THEN 2
<                 WHEN 'UNKNOWN' THEN 3
<                 WHEN 'WARNING' THEN 4
<                 WHEN 'CRITICAL' THEN 5 END)
<             WHEN 1 THEN 'OK'
<             WHEN 2 THEN 'PENDING'
<             WHEN 3 THEN 'UNKNOWN'
<             WHEN 4 THEN 'WARNING'
<             WHEN 5 THEN 'CRITICAL' END ST
<            FROM  rhn_probe_state PS,
<               rhnServerGroupMembership SGM
<          LEFT JOIN rhn_check_probe CP
<           ON SGM.server_id = CP.host_id
---
>          (SELECT  DECODE(
>                   MAX(DECODE(PS.state, 'OK', 1, 'PENDING', 2, 'UNKNOWN', 3, 'WARNING', 4, 'CRITICAL', 5)),
>                       1, 'OK', 2, 'PENDING', 3, 'UNKNOWN', 4, 'WARNING', 5, 'CRITICAL') ST
>            FROM  rhn_check_probe CP, rhn_probe_state PS, rhnServerGroupMembership SGM
98a78
>             AND  SGM.server_id = CP.host_id(+)


diff probe_queries.xml /root/probe_queries.xml.orig
23a24
>        rhn_probe P,
25,27c26
<        rhn_probe P
<   RIGHT JOIN rhn_probe_state PS
<     ON PS.probe_id = P.recid
---
>        rhn_probe_state PS
31a31
>    AND PS.probe_id(+) = P.recid