[Spacewalk-list] Corrected query for pgsql support

[Pierre Casenove]

hello,
Please find attached another patch for solaris support on pgsql.
On spacewalk 1.7 patch install fails due to:
- not using AS
- using NVL2

Please note that file backend/server/action/solarispkgs.py still
contains one query (remove) that needs to be fixed:
_query_remove = rhnSQL.Statement("""
    select distinct
           pn.name as name,
           pe.epoch as epoch,
           pe.version asversion,
           pe.release as release,
           pa.label as arch
      from rhnActionPackage ap,
           rhnPackageName pn,
           rhnPackageEVR pe,
           rhnPackageArch pa
     where ap.action_id = :action_id
       and ap.evr_id = pe.id
       and ap.name_id = pn.id
       and ap.package_arch_id = pa.id (+)
""")

If someone is willing to correct it...

Pierre
-------------- next part --------------
From a09c2813b5e9b111de11598c0535bd386f58b3f9 Mon Sep 17 00:00:00 2001
From: Pierre Casenove <pcasenove at gmail.com>
Date: Wed, 27 Jun 2012 11:56:42 +0000
Subject: [PATCH] Correct query for PGSQL, addressing:
 SQL Error generated: ('syntax error at or near "name" at character 41\n', 0, '\n    select distinct\n           pn.name name,\n           pe.epoch epoch,\n           pe.version version,\n           pe.release release,\n           pa.label arch,\n           c.label channel_label,\n           nvl2(c.parent_channel, 0, 1) is_parent_channel\n      from rhnActionPackage ap,\n           rhnPackage p,\n           rhnPackageName pn,\n           rhnPackageEVR pe,\n           rhnPackageArch pa,\n           rhnServerChannel sc,\n           rhnChannelPackage cp,\n           rhnChannel c\n     where ap.action_id = %(action_id)s\n       and ap.evr_id = p.evr_id\n       and ap.evr_id = pe.id\n       and ap.name_id = p.name_id\n       and ap.name_id = pn.id\n       and p.package_arch_id = pa.id\n       and p.id = cp.package_id\n       and cp.channel_id = sc.channel_id\n       and sc.server_id = %(server_id)s\n       and sc.channel_id = c.id\n')

---
 backend/server/action/solarispkgs.py |   24 ++++++++++++------------
 1 files changed, 12 insertions(+), 12 deletions(-)

diff --git a/backend/server/action/solarispkgs.py b/backend/server/action/solarispkgs.py
index 64cb9f5..1fca8ce 100644
--- a/backend/server/action/solarispkgs.py
+++ b/backend/server/action/solarispkgs.py
@@ -23,13 +23,13 @@ __rhnexport__ = ['install', 'remove', 'patchInstall', 'patchRemove',

 _query_install = rhnSQL.Statement("""
     select distinct
-           pn.name name,
-           pe.epoch epoch,
-           pe.version version,
-           pe.release release,
-           pa.label arch,
-           c.label channel_label,
-           nvl2(c.parent_channel, 0, 1) is_parent_channel
+           pn.name as name,
+           pe.epoch as epoch,
+           pe.version as version,
+           pe.release as release,
+           pa.label as arch,
+           c.label as channel_label,
+           case when c.parent_channel is not null then 0 else 1 end is_parent_channel
       from rhnActionPackage ap,
            rhnPackage p,
            rhnPackageName pn,
@@ -52,11 +52,11 @@ _query_install = rhnSQL.Statement("""

 _query_remove = rhnSQL.Statement("""
     select distinct
-           pn.name name,
-           pe.epoch epoch,
-           pe.version version,
-           pe.release release,
-           pa.label arch
+           pn.name as name,
+           pe.epoch as epoch,
+           pe.version asversion,
+           pe.release as release,
+           pa.label as arch
       from rhnActionPackage ap,
            rhnPackageName pn,
            rhnPackageEVR pe,
--
1.7.4.1