[Spacewalk-list] Modification of PGSQL queries

[Pierre Casenove]

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

Thanks in advance for the review.

Pierre


2011/8/3 Pierre Casenove <pcasenove at gmail.com>

> 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/20110809/22ca6fa3/attachment.htm>